This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Replace Calculated Fields with DAX
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.
Figure 971 Define a new measure.
Build the measure in the Measure Settings dialog. Use the Check Formula button to check the syntax.
Figure 972 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.
Figure 973 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.