At this point I think we’ve established pretty well that in DAX every sub-formula always runs with two lists in place: a list of all the values of the current row (“Row Context”) and a list of all the current filters (“Filter Context”). These two lists together are called the sub-formula’s “Evaluation Context”.

In the last post we learned that CALCULATE works by creating a new Evaluation Context with changes made to the “list of current filters” (Filter Context) and then runs a New Filters Formula within this new Evaluation Context. The changes you want CALCULATE to make to the filters are defined in argument 2, and the New Filters Formula is defined in argument 1.

Hopefully CALCULATE is already starting to get a little less mysterious at this point.

In this post I want to dive just a bit deeper into how we got out final number in the last post. It worked, and was at least somewhat intuitive; but might still feel a little bit mysterious and magical. There is no magic here however, so now I want to clear away more of the smoke and mirrors so you can see the simplicity behind how the “it just works” feeling is achieved.

Thankfully, this will be a little easier to deal with.

Back Up To Speed

If you’ve just read the last post feel free to skip this section. However if it’s been a little bit, or you think a recap might be nice, here’s what we did last time:

We used this Data Model because we always use this Data Model:

We were imagining a Measure called “Total Transactions All Shifts” running in a Power BI card with couple slicers nearby:

A user would expect that this Measure, based on its name, should count the number of sales transactions (rows) but ignore the slicer for “Lunch” (which is just one shift, not all the shifts).

Here’s the code for the Measure:

Total Transactions All Shifts = 
CALCULATE(
    COUNTROWS( Sale ),
    REMOVEFILTER( Sale[Shift] )
)

To understand how the Measure came up with that number of “3”, we decomposed its code by pulling the sub-formula out from the parent formula and noting the Evaluation Context for each:

The short version of what’s happening above is that CALCULATE took the default Evaluation Context (with two filters from slicers) made a copy of it; and removed the “Lunch” filter from the copy. Then in this new Evaluation Context the New Filters Formula was run.

In our New Filters Formula, the COUNTROWS function is expecting a Temp Table as an argument and we are giving it a Model Table (“Sale”), so DAX does the conversion for us by taking the Auto Snapshot of “Sale”. This produces a Temp Table that has been filtered down to “H. Special” rows. COUNTROWS then counts the rows of that Temp Table and produces the number “3”. That’s our Measure.

The intuition for why that Temp Table is filtered down to “H. Special” but not “Lunch” is not too tricky. The list of filters had an “H. Special” filter but not a “Lunch” filter. That’s true. But now let’s dig just a little deeper to see exactly what caused the results we got.

Domo Arigato Filter Roboto

Because I want you to start to feel how mechanical DAX is, I want to emphasize *how* this change to the Filter Context got us the results that we wanted in that Temp Table sitting in the top right corner of our illustration. Lemme show it to you again so you don’t have to scroll up:

I want to focus on how that Auto Snapshot of “Sale” came up with the 3 row Temp Table at the top.

What are the steps of the Auto Snapshot again? Oh right:

    (1) The Simple Copy – A copy of “Sale” is made as a Temp Table.
    (2) The Super Lookup – All related columns from “Dish” are added via the relationship.
    (3) Auto Filtering – All filters in the Filter Context are applied.

Here’s just the Auto Snapshot drawn out as it’s own stand-alone illustration. Pay attention to the Auto Filtering step (the one with the orange stuff):

See what happens in the Auto Filtering step? Because the Auto Snapshot is a part of the New Filters Formula, it uses the *new* Evaluation Context set up by CALCULATE; the one where the Filter Context no longer has a “Lunch” filter.

Therefore when the Auto Filtering step looks into the Filter Context to see what filters to apply, it just sees the one filter for “H. Special”.

This leads us to a meaningful question…

When we used CALCULATE to change the Filter Context, in which step of the Auto Snapshot did that change actually have an impact?

It was during that specific step of Auto Filtering.

The steps of “The Simple Copy” and “The Super Lookup” were both important, but neither of those steps used the Filter Context in any way. It was only in the last “Auto Filtering” step when the Filter Context actually got used for something. It was only in that last step where changes to the Filter Context had a direct impact.

Said differently, if it wasn’t for the specific step of Auto Filtering, changing the Filter Context would have had no effect at all.

Auto Filtering and the Filter Context

I will often say something like:
“The Auto Snapshot is affected by changes to the Filter Context.”

This is true. However, as we’ve seen in the last section, it is specifically the Auto Filtering *step* of the Auto Snapshot that is affected by changes to the Filter Context. This distinction is important for understanding how the Auto Snapshot works; but since there are many *other* functions that also perform Auto Filtering, what we’re describing here actually points to a more universal concept.

What are all the things in DAX that the Filter Context affects?

Auto Filtering.

That’s it.

Other than few obscure specialty functions, the only thing that Filter Context gets used for in DAX is Auto Filtering. That doesn’t mean the concept isn’t important, but it does mean that what it gets used for is both simple and straightforward:

Filter Context is the list of filters that gets applied during Auto Filtering.

Written out like that, the conclusion hardly seems profound. In fact, it probably sounds somewhat obvious. However, people will often go years without being able to understand Filter Context in such a crisp, clear way.

More Than Just the Auto Snapshot

While the Auto Snapshot is very important, it is hardly the only thing in DAX that performs Auto Filtering.

As I said in the last post, basic aggregators like SUM, MIN, MAX, etc, all also include an Auto Filtering step; which is why (or more accurately *how*) they respond to slicers “like magic”. Other important functions like VALUES, DISTINCT, and ALLSELECTED also include an Auto Filtering step which is why they too respond “like magic” to slicers.

Any and all functions that respond “like magic” to slicers *must* include an Auto Filtering step by definition. This step is how the magic is achieved in DAX.

(See the post script at the very end for a little more nuance on this.)

While there are many functions that perform Auto Filtering, a large number of them are quite obscure. The list of ones that I use with great regularity is reasonably short and mostly includes just the ones mentioned in the last couple paragraphs:

        The Auto Snapshot
        Basic Aggregators (SUM, MIN, MAX, AVERAGE, DISTINCTCOUNT, etc.)
        VALUES
        DISTINCT
        ALLSELECTED
        Time Intelligence Functions ( SAMEPERIODLASTYEAR, DATESYTD, etc.)

So, by definition, what’s above is the list of all the common functions in DAX that respond to filters. While it may not feel particularly important, it actually is.

To understand exactly why, we have to get a bit more practical.

Where There’s Smoke, There’s Fire

Most common DAX problems are related to filters. Probably 90% percent or more.

Since it is only the items in the above list that respond to filters, by definition they are any Measure’s main potential points of failure. Those items are “where Measures break” most of the time. They are not usually the root cause of the failure (which again, is filters), but they are where the failure becomes visible. To use a metaphor: they are not what’s on fire, but they are where you can start to see smoke.

Usually the actual root cause of your problem is almost always one of the following:
        “I forgot to add a filter”
        “I forgot to remove a filter”
        “I added a filter without meaning to”

However those root causes are almost impossible to spot directly.

What you can spot is a MAX function that’s supposed to be returning 25 and is instead returning 4,000,000. Once you spot the 4,000,000 you can ask yourself the question “What’s going on with the filters to give me that huge number?”, and start tracing backward from the MAX function to figure out what filters need to be added or removed to get you that 25 you were expecting.

So when you have filter problems, it is the items from the list in our last section (including MAX) that act like “the end of the thread”, allowing you to trace back and find the root cause of your issue.

So for me, when a Measure isn’t working, step one is almost always to locate all instances of items from the above list in my code. I then try and determine which of them might be responding to filters in a way I wasn’t expecting. Finally, I try to build a story about what might be problematic about the filters and use CALCULATE to adjust the filters accordingly till things start working.

After working with DAX for over a decade, that’s usually how I go about problem solving.

In fact, this “list of functions that perform Auto Filtering” is so important that I have a name for them: the Auto Filtering Functions. At the risk of being obvious, an Auto Filtering Function is any function that performs Auto Filtering. As a less technical “rule of thumb” these are any functions that respond to slicers.

So to be clear: when your Measure isn’t working in DAX, start by identifying all the Auto Filtering Functions. These are where problems with the filters show up, and where you can start the process of getting things working.

(One oddity that you probably noticed is that I’m calling the Auto Snapshot an “Auto Filtering Function” despite the fact that it, well, isn’t actually a function. This is the single exception where an Auto Filtering Function is not a function. I could have made the name longer to handle this one exception (“The Auto Filtering Functions and References”) but I like the shorter name. It conveys the idea, which is what I care about. Also, in fairness, the Auto Snapshot is a reference that *acts* like it has a function wrapped around it, so the Auto Snapshot is “sort of” a function.)

Contexts Redefined

After all this we can go back and make out definitions a little better.

Row Context is the list of values in the current row. Its main job is to allow for Current Row References, where a sub-formula can pull out values of the current row to be used as individual numbers, text strings, dates, etc.

Filter Context is the list of current filters. Its main job is to allow for Auto Filtering, so when the sub-formula contains an Auto Filtering Function, DAX knows which filters to apply automatically.

So if we have an Evaluation Context that looks like this:

The things on the left are numbers and text strings of “the current row” that you can pull out using Current Row References to add together, multiply, or do whatever you want with.

The things on the right are the Temp Tables acting as “the current filters” that get applied when you do anything that involves Auto Filtering (such as the Auto Snapshot or basic aggregators like SUM, MIN, or MAX).

There is one additional thing that can be done involving both Row and Filter Context that bears the rather intimidating name of “Context Transition”; it’s less scary than it sounds and I’ll be coming to that soon enough.

While all of this is interesting what you are probably actually wondering at this point is how you can get CALCULATE to do something more practical. Removing filters is fine (and you will do it often), but *adding* filters is what you will be needing to do far more often. In the next post we will start to cover this very important topic.

Till then,
-BG

—

Post Script: It’s a Kind Of Magic

Before I said that all functions that respond “like magic” to slicers must includes an Auto Filtering step by definition. Auto Filtering in this sense is how the “it just works” magic is achieved in DAX.

This is true but to be a bit more nuanced, any time Auto Filtering happens in DAX it is always the final step of the same *three step* process of the Auto Snapshot.

So any function that performs Auto Filtering, also performs “The Simple Copy” and “The Super Lookup” to lay the groundwork for the Auto Snapshot. The last step only works because of the first two.

Without “The Super Lookup”, filters on columns of related tables wouldn’t be filtered. Without “The Simple Copy” there would be no table onto which you could add columns in “The Super Lookup”.

All three steps together are needed to make the magic work. The first two are like the magician setting things up, and the third is like the magician actually pulling the rabbit out of the hat.

So if you look at the SUM/MIN/MAX/AVERAGE functions, the Time Intelligence functions, the VALUES function, the DISTINCT function, the ALLSELECTED function and the Auto Snapshot, every single one performs “The Simple Copy”, “The Super Lookup”, and “Auto Filtering” as part of their process. If they didn’t they wouldn’t respond properly to slicers.

What makes the Auto Snapshot so interesting (and why I spend such an absurd amount of time on it) is that it contains all three steps of the magic trick and nothing else. It’s the magic of DAX in its purest form.

If you can understand how the Auto Snapshot works, you can understand how all the other functions work. The other functions are essentially just the Auto Snapshot plus one or two other steps (which are usually pretty simple). That’s why understanding the Auto Snapshot is so central to everything in DAX.