57.1 DAX Formulas for PowerPivot – Chapter 8

I missed reading a chapter yesterday. I guess that will happen when you get a new toy

Anyway, on to chapter 8. Chapter 8 introduces the DAX CALCULATE() function. I’ve heard it said that if you understand CALCULATE() you can call yourself a DAX programmer.

The chapter starts off by calling CALCULATE() a “supercharged SUMIF().” I’m not coming to PowerPivot as an Excel expert so that didn’t mean a lot to me (sort of like how I didn’t get how relationships were much better than VLOOKUP right away). Rob starts with a simple example that shows how CALCULATE() works. Then he describes what happens when results are evaluated by the PowerPivot calculation engine. Grokking this stuff is really how you know how to build the calculations you need to do the analysis you want to do.

Continuing on, the chapter shows two useful examples of using CALCULATE() – analyzing transactions of a certain type and growth since inception. The chapter concludes with an explanation of how multiple filters in a CALCULATE() call behave and introduces the ALL() function which allows you to craft a filter that removes all the other filters. The last point the chapter makes is that you really need to understand the context of the cells that contain the data you are analyzing. Just because of the position of numbers in a PivotTable doesn’t mean that the numbers in a column will add up to the grand total of that column.


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