Guess Post by Scott Senkeresty
Rob is taking a much-needed vacation this week, so you get to hang out with me again. Hurray for you!
When we last Became One With Calculate, I said in comments that I would “work on a visualization/graphic”. I admit to spending far too much time trying to dream up the perfect visual, and kind of failing. I am sure the elusive visual exists, but for now, I would like to reinforce our understanding of CALCULATE() with a few more examples.
We will again be partying with the Adventure Works, against this simple measure:
[Total Sales] := SUM(Sales[ExtendedAmount])
Example 1: Column Filter
[TotalSalesEurope] := CALCULATE([Total Sales], Territories[Continent] = “Europe”)
This boolean parameter (aka: true/false parameter, column filter) says “Hey, Mr Dax Engine, I really don’t care what filter you had on Continent… cuz now it is Europe”. Of course, we did nothing that would impact a filter on Product[Category], so each of the categories still have their own total sales.
I must admit… when I last wrote about CALCULATE(), I was thinking there was something fundamentally different and special about these true/false filters, compared to the table-style filters such as we see with FILTER() or ALL().
And indeed, they are kinda sorta almost special… in that they have a cute syntax and they have the potential to be much more efficient (in terms of speed).
However, functionally, the above measure is identical to the following measure:
FILTER(ALL(Territories[Continent]),Territories[Continent] = “Europe”))