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.