skip to Main Content

Guess Post by Scott Senkeresty

Intro

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”)

imageThis 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:

=CALCULATE([Total Sales],
          
FILTER(ALL(Territories[Continent]),Territories[Continent] = “Europe”))

So, the true/false filters are not really that special.

A minor note that including the column name on  ALL(Territories[Continent]) is required, else the ALL() would remove filters on every column of the Territories table, not just filters on the continent.

Example 2: FILTER() without ALL()

=CALCULATE([Total Sales], FILTER(Territories[Continent], Territories[Continent] = “Europe”))

imageI just took the exact same measure from the last section, and removed the ALL().  That is all I changed.  Pinky promise.

Here is what you need to remember:  When evaluating the filter parameters to CALCULATE, you start with the original context.  So, when DAX is evaluating the cell at (Clothing, North America) and the FILTER() is applied… the filter content of {Category=Clothing, Continent=North America} is alive and well.

What the ALL() in the previous section did was simply remove the {Continent=North America} filter.   But we don’t have that ALL()… and therefore our FILTER on {Continent=North America} is still alive.

Naturally, when we look at rows where Continent=North America (from the original filter context), we don’t exactly find many where Continent=Europe (from our expression in the FILTER)… so we get a blank.

Question:  Does this help?

=CALCULATE([Total Sales],
          
FILTER(Territories[Continent], Territories[Continent] = “Europe”),
           ALL(Territories[Continent]))

No, Silly.  I asked you the same thing last time, too.

The evaluation of the FILTER() results in filters { original filter context + Continent=Europe }.  The evaluation of the ALL() results in filters { original filter context with Continent filter removed }.

Every filter parameters to CALCULATE are going to be combined in a logical AND.   This is where I really wish I had a pretty visual… but I think you can see (alas, only in your mind’s eye) that { Continent=Europe } and { No Filter on Continent } … is still going to end up with an overall filter context where Continent=Europe.

Adding the extra ALL() has no impact.

Example 3:  Two ALL()

=CALCULATE([Total Sales], ALL(Territories[Continent]), ALL(Products[Category]))

image

No big secret here.  The first ALL() strips away any filter on Continent, and the second ALL() strips away any filter on Category.  Every cell ends up looking like the grand total.  Totally expected, understandable, non-tricky, etc.  Some extra cells show up… they apparently didn’t fit nicely into a Category/Continent and we filtered ‘em out before.  Fine.  I’m still happy.

But, here’s the problem.  While it reads great, and I feel non-surprised by the results and such… it also secretly confuses me if I think too hard about it.

<Beware!  Begin Questionable Journey Into Scott’s Brain!  Turn back now!>

The result from the first ALL():
{ original filter context with Continent filter removed }

The result from the second ALL():
{ original filter context with Category filter removed }

If you think about this just right (errrr, just wrong I suppose)… the 1st filter parameter still has filter on Category.  That was in the original filter context… and we only removed the filter on Continent, right?   Similar situation on the 2nd filter parameter, but fields reversed.

Still in PleaseDontThinkAboutItLikeScottDoesLand… when we are calculating the (Clothing,North America) cell… that is our original filter context.  So we end up with :
1st ALL:  { original filter context of (Clothing + North America) and remove Continent Filter }
2nd ALL: {
original filter context of (Clothing + North America) and remove Clothing Filter }

It feels like the combined filter after we do the logical AND of all filter parameters should be: { Clothing } + { North America }… meaning, nothing really changed, the filters shouldn’t do anything. 

But, just look at the table people.  That is clearly NOT what happens.  So, um, don’t think about it like that Smile 

If Rob was around, he would basically kill me for even getting this near your brain, causing potential confusion, so please don’t tell him.

(Surprise Note from Rob:  Greetings from Puerto Rico!  Yeah, when using a function like ALL or FILTER as an input to CALCULATE, I like to think about that function as DOING something to filter context – ALL is a verb in that case.  I never think about it as creating a table – that might be true but it is confusing, as evidenced by our journey into Scott’s Brain.  When I use a function like ALL as the input to a COUNTROWS, however, THEN I think of it as a table – as a noun.  This all clarified for me when I was making the “Filter Purpose vs. Table Purpose” section of PowerPivotPro University, and I’ve never looked back.)

I suppose the better way to think about this is in terms of “filter context modifications”.  The first ALL() will “remove any filters on Continent” AND the second ALL() will “remove any filters on Category”. 

But then I look back at the “trick question” from Example 2 (adding an extra ALL())… and I kind of want that to work…

<End Questionable Journey Into Scott’s Brain>

Conclusion

I’m sticking with my theory that CALCULATE() is the most important function in DAX, so a bit of reinforcement is always a good idea.  You got 3 parts of reinforcement here, and 1 part of… something a bit less crisp that we want.

We took a turn into some sketchy areas (of my brain), and here is what I hope will happen… lively discussion in the comments!   You folks get it all worked out, then we can all improve our mental models… and our our DAX models!  If you can straighten me out, hopefully I can draw that pretty visual.

This Post Has One Comment

Leave a Comment or Question