The DAX formula language really shines when you use it to create a new measure for your pivot table. DAX measures are in the same genre as calculated fields, but are infinitely more powerful.
To create a new measure, select New Measure from the PowerPivot tab in Excel.
- Define a new measure.
Build the measure in the Measure Settings dialog. Use the Check Formula button to check the syntax.
- Define a new measure.
Excel will calculate the measure once for every value cell in the pivot table. In the figure below, this means that the calculation will happen 39 times. This is faster than adding the calculation to a million rows of source data.
- Excel calculates the formula 39 times in this pivot table.
I've written a complete DAX reference in my PowerPivot for the Excel Data Analyst book, and I won't attempt to replicate that guide here. However, the following topics are the a-ha moments in my power pivot learning curve.