In this quick post we’re going to continue looking at examples of adding basic filters with CALCULATE. I don’t want to get too bogged down in this post, there are few (if any) new concepts here; but seeing multiple examples can be very helpful when encountering tricky new ideas.
With that in mind, we’re going to walk through three examples of Measures that add simple filters with CALCULATE, Quick Tables, and TREATAS.
Here are all three in use on a Power BI Report:
We’ve got “Total Pasta or Salad Transactions”, “Total Pasta-Lunch and Dinner-Salad Transactions” and “Total Lunch and House Special Transactions”.
All three will use the same approach:
(1) Create a Quick Table using the curly braces.
(2) Use TREATAS to rename the columns so they will filter properly.
(3) Use CALCULATE to add the Temp Tables as filters.
(4) Use an Auto Snapshot and COUNTROWS to get the final answer.
Notice that in the above image both slicers have zero items selected. The implication of that for our examples is that every Measure’s initial Filter Context will be totally empty. This simplification will let us focus on just the key concepts we care about.
So now, one at a time, let’s decompose these Measures to see how they work.
(If you’re coming to this post fresh, I recommend first reviewing my most recent post where I introduce the concepts we’re working through here. All of the following will make much, much more sense if you do.)
Example 1: Who Needs Burgers Anyway?
We’re going to be using the same Data Model we always use:
Our first Measure is going to count how many sales transactions (rows) there were for people getting either pasta or salad. Here’s what the end result looks like in Power BI:
Again, notice that the user has not clicked on any slicers. This will mean the initial Filter Context will be completely empty; this will keep us from having to juggle too many concepts at a time.
OK, let’s look at our Measure:
Total Pasta or Salad Transactions =
CALCULATE(
COUNTROWS( Sale ),
TREATAS( { "Pasta", "Salad" }, Sale[Dish] )
)
Let’s take the above and start with a simple decomposition where we break the main formula and the sub-formula out into separate boxes:
So at it’s simplest, the above Measure is going to use CALCULATE to create a modified Evaluation Context where the filters have been changed, and then run the New Filters Formula in that new Evaluation Context. The main formula is in the left panel, and the New Filters Formula is in the right panel.
OK, let’s draw out the rest but leaving a couple parts unfinished for us to fill in as we go:
OK, so there are three things missing from the above image (indicated with “???”). Those include the Temp Table created by TREATAS (top left), the new Evaluation Context created by CALCULATE (bottom right), and the Temp Table from the Auto Snapshot of “Sale” in the New Filters Formula (top right).
Let’s fill them in one by one, starting with question marks in the top left. What should be in that bubble is the Temp Table produced by TREATAS; that Temp Table is what we are handing to CALCULATE to add as a filter. Lemme add it:
A simple 1×2 Temp Table is what the TREATAS function produces and gives to CALCULATE. What happens when you give CALCULATE a Temp Table? It adds it as a filter to the new Evaluation Context which should be where those question marks in the bottom right are. Lemme add that in:
The new Evaluation Context is a copy of the old one, but with the Temp Table we gave it added as a filter. Since the new Evaluation Context is set up, let’s look at our New Filter Formula and the last set of question marks.
The Auto Snapshot of “Sale” is going to produce a Temp Table based on “Sale” with all related columns added (“Super Lookup”) and filtered by all the filters in the Filter Context (“Auto Filtering”). Lemme add that:
The Temp Table from the Auto Snapshot has been filtered down to just “Pasta” and “Salad” rows. When COUNTROWS is given this Temp Table it produces the number 4 which is how we got the number in our card:
Now lemme show you the whole thing at once:
Take a second to see if you can’t work through what we just did in your head. You don’t have to get it right; just give it a shot.
OK, got it? One example down, two to go.
Example 2: Heavy Lunch, Light Dinner
Here we’re going to look at a Measure that tells us how many rows there were for either:
Pasta at lunch
or
Salad at dinner
So this is a filter for specific *pairings* of dishes and shifts. Keeping just certain combinations across two columns like this requires the filter itself to have two columns. You won’t get tons of requests like this, but they do come up occasionally and it’s valuable to see how they affect Auto-Filtering.
(FWIW, if you’ve ever used a hierarchical slicer, that slicer works by doing almost the exact same thing that we’re about to do.)
Here’s what the final result looks like in Power BI:
Again, no slicer selections and therefore an empty initial Filter Context for the Measure. We want to figure out how that Measure came up with “3” as an answer. Speaking of the Measure:
Total Pasta-Lunch and Dinner-Salad Transactions =
CALCULATE(
COUNTROWS( Sale ),
TREATAS(
{
( "Lunch", "Pasta" ),
( "Dinner", "Salad" )
},
Sale[Shift], Sale[Dish]
)
)
Great, again let’s start with a simple decomposition, breaking the main formula and the sub-formula out into separate boxes:
Now let’s draw out the whole thing but with some missing puzzle pieces:
Like before, we’ll start by adding the Temp Table created by TREATAS (top left). The parenthesis inside the curly braces indicate that this Temp Table will have multiple columns. Can you imagine what the Temp Table looks like?
OK, lemme add it:
So that’s the 2 row by 2 column Temp Table CALCULATE is going to add as a filter in the new Evaluation Context. Speaking of which, let’s next look at that new Evaluation Context (bottom right). You can see the old one just on the other side of the little divider; with that in mind, can you guess what the new one looks like?
OK, lemme add it:
The new Evaluation Context is a copy of the old one with the Temp Table from the top left added as a filter. This is the Evaluation Context the New Filters Formula will run in. When it does, the COUNTROWS functions is going to count the rows of the Auto Snapshot of “Sales”. That will be the last missing puzzle piece in the top right. Looking at the filters in the Filter Context, can you imagine what that will look like?
Got the image in your head? OK, good, now lemme draw it:
That’s the Temp Table that is getting handed to COUNTROWS. Notice that there are 3 rows that made it through the Auto Filtering process. Each row is either the combo of “Lunch” and “Pasta” or the combo of “Dinner” and “Salad”; that’s the behavior you get with a multi column filter, it keeps the listed combinations that appear in the filtering Temp Table.
When COUNTROWS gets that Temp Table it returns the number 3 which is our final answer:
OK, let me show you the whole thing again. See if you can’t work through the whole process in your head:
OK, great. 2 Examples down, one more to go.
Example 3: Blast From The Past
Third times the charm. In our last example we’re going to look at a Measure that counts transactions for Lunch where people bought the House Special.
Here it is in Power BI:
Notice (yet again) that the slicers off to the right have not been clicked on. If you’ve been following this blog a while you might remember that we often used to set things up by having “Lunch” and “H. Special” selected in the slicers. In this example, rather than adding those filters with the slicers, we will add them using CALCULATE.
(Though we’re not there yet, eventually you will learn that mechanically, the way slicers actually add filters to the initial Filter Context is using a process almost identical to what I’m about to show you.)
Here’s our code:
Total Lunch and House Special Transactions =
CALCULATE(
COUNTROWS( Sale ),
TREATAS( { "Lunch" }, Sale[Shift] ),
TREATAS( { "H. Special" }, Dish[Type] )
)
Probably the biggest different here from earlier example is that we are giving CALCULATE a third argument. Any argument beyond the first argument is going to be understood as a Filter Modification argument. If you gave CALCULATE 50 arguments, the first would be the New Filters Formula, and the next 49 would be different Filter Modifications. We’re giving CALCULATE 3 arguments, so the first is the New Filters Formula, and the next two are Filter Modifications.
So with both those last two arguments we are providing instructions for creating Temp Tables, CALCULATE is going to follow those instructions to build the two Temp Tables, then add each to the Filter Context. Easy as that.
We’re getting ahead of ourselves though.
Let’s start with a simple decomposition, breaking the New Filters Formula out from the main formula:
Let’s add in our placeholders for our missing puzzle pieces around both what the Temp Tables look like and what the new Evaluation Context looks like:
We’re on the final example; so it’s worth taking a second to see if you can fill in the missing pieces on your own before I do it for you. No big deal if you can’t do all or even part of it; simply trying will help convince your brain that this skill is something you need to get good at. Go ahead and give that a shot.
OK, now let’s add those Temp Tables from TREATAS that we are handing to CALCULATE. They’ll be in the top left corner:
Groovy. Since those Temp Tables are what we are giving to CALCULATE, the new Evaluation Context should have them added as filters. Lemme draw that into the bottom right corner:
So that’s new Evaluation Context that the New Filters Formula will run in.
The New Filters Formula contains the COUNTROWS function which contains a reference to “Sale”. Since COUNTROWS wants a Temp Table and we are giving it a Model Table, DAX does the conversion for us by taking the Auto Snapshot of “Sale”. The Temp Table that comes back will be Auto Filtered by all the filters in that new Filter Context.
Let’s look at that Temp Table that the Auto Snapshot produces (top right):
Because the Auto Snapshot happened in the New Filters Formula, it took the new Evaluation Context into account when performing Auto Filtering. This is why only the two rows for “Lunch” and “H. Special” came back. This is the Temp Table that COUNTROWS is going to work with.
When COUNTROWS gets that Temp Table, it counts the rows in it and returns 2; our final answer. Let’s compare that to our initial card:
Sure enough they match up.
Let’s look at the final version; see if you can walk through how it works in your head:
There’s a fair amount going on above, but hopefully you can start to see how this is all very mechanical and to some degree repetitive.
Final Thoughts
The bad news with DAX is that the introductory stuff is quite hard (if you’re doing it right). Simple things like adding a filter require a large foundation of knowledge to both execute and understand. We’re a dozen articles in and we’re just now covering what usually shows up at the very beginning of most trainings, include my own (albeit in a simpler form). The reason the introductory stuff seems so hard is because DAX thinks like a database rather than like a human. This is good for performance, but less good for new DAX authors who in my experience are mostly humans.
The good news with DAX is that the intermediate and advanced stuff is relatively easy. Once you master the tricky concepts required to fully understand basic things like “adding a filter”; performing more complex tasks like “finding year to date sales” is mainly an exercise in writing longer measures that combine the same handful of simple concepts in different ways.
All of this is to say (if you’re doing it correctly) the hardest part of learning DAX is at the very beginning. We’re quickly approaching the point at which all the really hard things have been learned, and things start becoming easier and easier.
We’re not all the way through the woods yet though. In our next post we need to tackle the scary sounding concept of Context Transition. If you understand Evaluation Context properly, it really isn’t that tricky. At this point you’ve been set up properly, and it’s time to finally jump in.
Till next time!
-BG
Brian Grant on BI 