57.1 DAX Formulas for PowerPivot – Chapter 6

Chapter 6 is the first “long” chapter in the book. It introduces DAX measures. Measures are a very powerful feature of DAX and the tabular model but they are also one of the most deep and complicated parts of PowerPivot, hence there’s a lot more to say about them.

Rob recommends adding measures from the PowerPivot field list in a PivotTable (you can also add them in the measure grid of the PowerPivot window but Rob’s point is that the context of a PivotTable is where you’ll use a DAX measure so you should work with them in that context). So the chapter starts with adding a PivotTable and then adding a measure to the PivotTable.

In PowerPivot, there are two kinds of measures: implicit, which you add by adding a numeric field to the Values area of the field list; and explicit, which you add using the New Measure command in the PowerPivot ribbon or from the context menu in the field list. Rob’s an explicit guy and recommends that we be the same.

After adding a simple, stand-alone measure, the chapter builds by adding a measure that references another measure and then shows how changes to the original measure affect the dependent referencing measure. He talks about the benefit of this: if you have a fundamental calculation in a base measure that multiple other measures reference, if the base measure’s calculation has to change then all the referencing measures take advantage of the update without having to track down and modify each place that the calculation is used if you had instead created your model by hard-coding the calculation everywhere it’s needed in your model. Obviously this implies that you have to take care when designing your model. A couple of benefits of measures are mentioned: you can use them in any PivotTable in the workbook and you can centrally define how you want the measure to be formatted.

The chapter ends with an example of using the DAX COUNTROWS() and DISTINCTCOUNT() functions to calculate average of sales per transaction and the number of sales per day and then uses those measures in a PivotTable. He then shows how the measures still work when you rearrange your PivotTable – “Write once, use everywhere” formulas.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s