Dax Measures in PowerPivot
Dax Measures in PowerPivot
Measures are the one area that is truly new. Although Calculated Fields in traditional Pivot Tables are similar this is where most of the power of PowerPivot exists.
As an example, with traditional Pivot Tables you may encounter a situation like this
Several years worth of monthly data. As nice as this is it would be more ideal if we could have the various months side by side so that we can compare growth over the periods. We want to do the following
WIth traditional pivot tables this would involve some workarounds or playing with the underlying data. Traditional Pivot Tables would not allow data for 31/08/2010 to sit on the same line as data for 31/08/2011.
In PowerPivot this is a simple DAX measure which tells the Pivot Table to show the data from the SamePeriodLastYear (this is not a punctuation error, this is actually one of the new formula in PowerPivot).
Once you have them side by side it is easy to create meaningful graphs and growth statistics, and best of all it will automatically update when you refresh the data!
Creating DAX Measures
In order to create a Measure in a PowerPivot pivot table you need to use the PowerPivot ribbon (not the Pivot Tables ribbon normally used to create calculated fields in pivot tables). In Excel 2010 it is called a Measure and in Excel 2013 it is a Calculated Field.
Advantages of Measures:
- You can specify the type of formatting to always use for that measure. You do not need to redo formatting every time you incorporate it into a pivot table
- The measure is available in all the Pivot Tables (and the formatting mentioned above will be the same)
- It takes into account any special filters you have specified in the measure AND where it is placed in the Pivot Table to calculate the correct number that meets all the conditions
The rules of DAX Measures
The way that Excel and PowerPivot thinks through a DAX measure is important. Below is our take on it. As you progress through this section you will see how this thinking is used.
- First it looks at the Pivot Table it is in and considers the row, column, report and slicer options.
- It then looks into the formula you have written and applies the further filters. If there is a conflict the formula wins.
It then performs the expression on the resultant data (add, average, disctinctcount etc.)
Suggested Process to create a report with DAX Measures
A good way to create a PowerPivot report is to follow the following process:
- On a piece of paper design what you want the report to look like
- Create a Pivot table but pull through all the columns you would need if you were going to create the report manually (even if you don’t want them in the final report). This is just so that you can visualise what Excel is seeing and using
- Now create the formula taking into account what you see. Depending on what you are trying to do you may use many of the formula but the key to most reports is the CALCULATE formula.
- Once the formula are doing what they are supposed to, you can remove some of the original column fields to get back to your ideal report.
If you use this process you will build your reports quicker with less iterations. You will also understand what Excel is thinking.