The most important skill in DAX is Table Vision.

Table Vision is being able to look at your code and envision what tables you are asking DAX to create, and what you are asking it to do with them. Not just describe them, but *see* them in your mind’s eye. The stronger your Table Vision is, the easier DAX is to work with.

One big challenge is that with one of the most common things you do in DAX, because of some peculiarities of the language, it is very easy to *incorrectly* envision what is happening. Said otherwise, the drawing you are making in your head is wrong.

An example might help here:

Let’s say you load two tables into Power BI, one called “Sale” with your sales transactions, and another called “Dish” with all the dishes you sell. You also create a relationship between the Dish column of the first table and the ID column of the second table:

Now imagine you look at a bit code like this:

COUNTROWS( Sale )

When your eye crosses over the “Sale” part, the natural inclination is to envision the table with that same name that you loaded into the Data Model.

Like this:

But (very counter intuitively) what you need to envision looks more like this:

A variation on the first table, but with more columns, fewer rows, and a slightly different style because it’s a different *kind* of table. This is the sort of thing typing “Sale” in the Measure above gives you.

Confused? I don’t blame you; this is indeed very confusing.

The reason for this confusing behavior relates to a feature in DAX that might formally be called Implicit Evaluation, but I like to call it The Auto Snapshot. (We’ll get to a proper definition in a bit.)

If you understand the Auto Snapshot, you will be able to envision the correct version of the table in your head when you look at your code. This will make understanding the behavior of functions like COUNTROWS, or SUMX, or GROUPBY much easier. If you envision the wrong version, understanding the behavior of everything that comes next will be near impossible.

The following series of posts will be about helping you see things the right way, and understanding why they look the way that they do. This first post will be about the basics and providing the “why” of the Auto Snapshot. In the next post I will walk you through the mechanics of how it works. Then I will show some examples of how understanding it makes understanding DAX easier. And lastly I’ll help you get started envisioning it in your head.

This may at first glance seem like a purely academic venture, with no practical applications, but the concepts we are about to discuss will show up in virtually every Measure you will ever write, whether it’s easy to see or not. Understanding this tricky concept up front will make learning the rest of DAX much easier and less complicated.

So with that, let’s get to it.

The Auto Snapshot (Implicit Evaluation)

So let me draw out that same Data Model with a few more annotations:

Again: a table called “Sale” with all our transactions, a table called “Dish” with the different dishes we sell, and a many to one relationship between the two tables based on the dish sold.

(Note that above each column I’ve written out the longer “Fully Qualified Name” of each column (“Sale[Qty]” vs just “Sale”); in actual code these longer column name are what you type in. Note also that in between the tables is a little “LU” which stands for “Look Up”; as in the relationship allows us to use the dish in the “Sale” table to look up the type and cost from the “Dish” table.)

Let’s also imagine we have a Power BI page with the following card and slicers on it:

Cool, so when filtered down to “Lunch” and the “H. Special” (House Special) the total number of sales transactions is two.

Good start.

You can probably arrive at this number looking at the Data Model:

The table on the right tells me that the house special is the burger. If I look at the table on the left, only two rows (#2 and #4) are ones where people bought a burger during lunch. So when the Measure returns “2” that number is correct.

How does the Measure arrive at the answer though?

Let’s start by looking at the formula of the Measure:

Total Transactions = COUNTROWS( Sale )

OK, not too complicated.

Dumb question:
What are we counting the rows of?

Uhm, it sure looks like we’re counting the rows of the Sale table.
Right?

I mean that’s kind of obviously what we’re doing.
Right?

It definitely has to be that.
Doesn’t it?

Nope!
The Sale table is NOT the table we are counting the rows of.

The table that is having its rows counted is *based* on the Sale table, but it’s not the Sale table. It has more columns than the Sale table, and fewer rows than the Sale table.

So let me bring up the images of the two tables from before and put them side by side:

The table on the left is the table called “Sale” in our Data Model and the one on the right is the table whose rows are being counted in our measure.

Before your brain properly explodes, I’ll point out that however we got the weird Frankenstein table on the right, counting its rows gave us the correct answer. With slicers set to lunch and the house special, there are indeed two transactions. That’s correct.

If you’re like me though, your brain won’t stop shouting:
“But that’s not what we asked for! We asked DAX to count the rows of the thing on the left. Why isn’t DAX counting the rows of the thing on the left?”

Look again at our code:

Total Transactions = COUNTROWS( Sale )

Why is DAX adding columns to and removing rows from the Sale table before counting how many rows it has?

There’s *nothing* in our code that indicates DAX should do that.

OK, what we’ve run into is a concept that is so ubiquitous in DAX that it’s actually tricky to spot. Folks are actually most likely to spot it the first week they are working with DAX; but they quickly learn to actively ignore it so as not to descend into madness.

As I stated before, the concept might formally called Implicit Evaluation; my name for it though is the Auto Snapshot. As the less formal name implies, it’s almost like you’re taking a picture of the table. That’s actually a pretty good metaphor for it.

Here’s an actual definition for you:

Whenever a function is expecting a Temp Table as an argument, but you instead provide just the name of a Model Table, DAX automatically creates a Temp Table for you using a very specific process called either “the Auto Snapshot” or “Implicit Evaluation”.

In this process, first DAX creates an exact copy of the Model Table as a Temp Table, to which DAX then adds all possible lookup columns it can across relationships, and finally, DAX automatically applies all filters from the Filter Context.

This Temp Table with added columns (from the lookups) and fewer rows (from the filtering) is what DAX actually gives to the function.

The Auto Snapshot

Obvious right? Maybe not so much. Let’s simplify…

Automatic Conversion Plus Other Stuff

So basically, in DAX there are two kinds of tables we care most about: Model Tables and Temp Tables.

Model Tables are, well, the tables in the Data Model. These are what you load into Power BI and what you can look at with the user interface. For most new users, when they hear “table”, this is what they think of. Raw data needs to be stored in this version. The longer name for these are “Physical Tables of the Data Model”, or just “Physical Tables”, but I tend to prefer “Model Tables” because these are always just the tables in your Data Model.

Temp Tables are tables that only exist while DAX is coming up with an answer to something (like a Measure). These are much lighter weight and more flexible than Model Tables, and DAX wants to get data in this format as quickly as possible. These are also called a “Table Value”, but that name can be confusing and scary sounding to new users. For lots of documentation, when it says “table”, this is what they are talking about.

If a function wants a Temp Table but you instead give it a Model Table, DAX automatically does the conversion for you. And critically, when it does the conversion for you, it throws in a couple extra steps to make your life easier. These extra steps are what cause the final result to usually have more columns and fewer rows than the original.

To make this a smidge more concrete, let’s look at some examples of functions that take tables as arguments and focus on which of the two *types* of table they require:

1. REMOVEFILTERS( <Model Table>                     )

2. ALL(           <Model Table>                     )

3. COUNTROWS(     <Temp  Table>                     )

4. SUMX(          <Temp  Table>,  <Per Row Formula> )

5. FILTER(        <Temp  Table>,  <Per Row Formula> )

6. TREATAS(       <Temp  Table>,  <Model Column>    )

With the first 2 examples, the functions are expecting a Model Table for the first argument. By “Model Table”, we mean the name of a table in the Data Model. So when you type “Sale”, you are giving the function the exact type of table it expects; therefore no conversion needs to be done.

With the last 4 examples though, the functions are expecting a Temp Table for the first argument, not a Model Table. So when you type in the name of a table in the Data Model like “Sale”, the function isn’t set up to work with this kind of table, so DAX does the conversion from Model Table to Temp Table for you, creating a Temp Table *based* on the Model Table. (Maybe 95% of the functions that want tables in DAX want Temp Tables rather than Model Tables, so this behavior is the rule rather than the exception.)

Importantly, when DAX does this automatic conversion for you, DAX throws in a few transformations without asking you: DAX uses the relationships of the table to essentially pull in or “lookup” all the columns from related tables, and then leverages these new columns to automatically apply filters from things like slicers.

Both the conversion itself and the extra steps are performed automatically by DAX to give authors the outcome they want without having to understand a lot of tricky concepts or write long verbose code.

I do want to walk you through the mechanics of this Auto Snapshot in detail (it’s easier than you think); but before I do that, I want you to really understand the motivation for *why* all this needs to happen. Doing so will make all this weird automatic behaviors make a lot more sense and feel a lot less random. DAX isn’t messing with you, it’s trying to be your friend.

To accomplish this it can be helpful to think backwards by imagining a world where DAX *does not* do all this stuff for you automatically, and see just how clunky the language becomes to work with. That’s where we’re going next.

Bizarro DAX

To explore the motivation for this, let’s imagine an alternate universe where DAX works just a little differently and does way less stuff for you automatically; in this alternate universe we’ll call this new language “Bizarro DAX.” Unlike regular DAX, Bizarro DAX does much less stuff for you automatically.

(To be clear, Bizarro DAX is not real. Don’t try to recreate the code in this section as most of the functions only exist in Bizarro DAX.)

Imagine in this alternate universe there’s a new Bizarro DAX author named Jamie J., and she writes what she think should be an easy Bizarro DAX Measure:

Total Transactions = COUNTROWS( Sale )

And when Jamie drops it on a card, she immediately get an error:

When she clicks on “See Details”, the error is something along the line of:

ERROR: The COUNTROWS functions requires a Table Value (Temp Table) as an argument. Please provide an evaluation function around your table reference.

Huh?

OK, a little research and Jamie discovers that in Bizarro DAX you need to take the reference to the Model Table “Sale” and use the function EVALUATE to convert it from a Model Table to a Temp Table before giving it to COUNTROWS.

OK, so the Jamie rewrites their Bizarro DAX Measure like this:

Total Transactions = 
COUNTROWS( 
    EVALUATE( Sale ) 
)

She doesn’t really understand *why* she needs to rewrite it like this; but when she drops it on a card, everything looks as expected:

Finally! Break out the champaign!

Then Jamie uses the slicers to select “Lunch” and “H. Special”, expecting the number to drop with each click. But with each click the number stayed exactly the same:

What? What’s going on?!? Why don’t the slicers do anything?

After fuming for about ten minutes, Jamie does some more research on Bizarro DAX and she finds out that for those slicer filters to be applied in Bizarro DAX she has to include function called APPLY_FILTERS that actually applies the slicer filters. It looks like this:

Total Transactions = 
COUNTROWS( 
    APPLY_FILTERS( 
        EVALUTE( Sale )
   ) 
)

What a giant pain. But at least it’s done.

Except it’s not. It’s still broken (of course):

The number moved, which is an improvement. But Jamie can just look at the data and see that if you only count the “Lunch” and “H. Special” (burger) rows, you should only have 2 transactions, not 4:

After some testing, it looks like the filter for “Lunch” has been applied, but the filter for “H. Special” wasn’t.

So now not only is it broken, it is also inconsistent. Great.

More research, more walks around the blow off steam.

It turns out that in Bizarro DAX, because the filter for “H. Special” is on a different table than the Sale table (it’s on the Dish table), that filter wont be applied unless Jamie changes their code some more. To get it to work, in between the existing APPLY_FILTERS and EVALUTE functions, the Jamie has to insert yet another function named ADD_COLUMNS_FROM_RELATED_TABLES.

Yikes. Even the name is terrifying.

The forums say something about this function adding a bunch of extra columns to the Temp Table that, somehow, some way, is the secret sauce to making everything work.

It looks like this:

Total Transactions = 
COUNTROWS( 
    APPLY_FILTERS( 
        ADD_COLUMNS_FROM_RELATED_TABLES(
            EVALUATE(
                Sale
            ) 
        )
    ) 
)

Now it works. Finally.

But this is a terrible experience! And look how ugly the measure is!!!

Jamie started writing the measure at 2:00pm and now it’s 7:00pm. Work ended 2 hours ago and their family is at home wondering where they are.

What should have been a simple measure taking 1 line of code and 1 minute to write ended up taking 10 lines and 5 hours. If someone asks how the measure works, Jamie’s answer is going to be “I have no idea, and don’t ask me to look into it”.

From a pure “Functional Language” point of view this Bizarro DAX version actually makes lots of sense; the program only does exactly what you tell it to and nothing more. All the various steps in the process are clearly represented as different functions.

But DAX isn’t the final project in a college course on functional programming; it’s supposed to be a self service language for non programmers. It needs to be easy, at least for the simple stuff.

It needs to just work.

DAX Makes It “Just Work”

Rather than requiring all that code to do something simple, DAX (the non-Bizarro, non-alternative universe version) let’s you type in the simple thing, and does the fancy complicated work behind the scenes without telling you, to give you the answer you want.

So when Bizarro DAX insists that you write this monstrosity:

Total Transactions = 
COUNTROWS( 
    APPLY_FILTERS( 
        ADD_COLUMNS_FROM_RELATED_TABLES(
            EVALUATE(
                Sale
            ) 
        )
    ) 
)

Regular DAX lets you get the same behavior with just this:

Total Transactions = COUNTROWS( Sale )

When you type in “Sale” to a function expecting a Temp Table, the three things represented by those extra functions in Bizarro DAX automatically get done to the “Sale” Model Table for you. This happen so you get the results that you expect without using 10 lines of code and 5 hours of your life.

What this means though, is that if you want to understand DAX, you have to be able to spot every single Auto Snapshot very quickly, see the three steps it performs, and envision (as best you can) the Temp Table that the function is actually being given to work with. I’ll show you some examples of how this helps eventually, but first we need to understand how the thing works. Let’s start with some better names.

Here’s my slightly nicer names for the three steps of this “Auto Snapshot”:
(the more technical names for each step are in parenthesis)

The Simple Copy (Table Evaluation)
An exact copy of the Model Table is created as a Temp Table

The Super Lookup (Table Expansion)
Every possible column that can be looked up across relationships is added to the first Temp Table.

Auto Filtering (Natural Semi Joining)
All filters in the Filter Context are automatically applied to this new widened Temp Table. Keeping just the rows that the end user expects. The extra columns from the last step are what let filters not on the main table still filter properly.

Despite the technically sounding names in the parenthesis, none of these are terribly difficult to understand. Further, while these actions definitely happen with the Auto Snapshot, they also happen with other functions too (examples include VALUES, SUM, MIN, MAX, etc). It’s actually pretty uncommon to write a DAX measure where the above three things don’t happen together at least once.

In that sense you can correctly think of these concepts as foundational to DAX. Understanding them, will make understanding everything the comes later much easier.

That might be enough for today though; in the next post we will explore the actual steps in greater detail. Stay tuned!

-BG

Post Script: On Being Overwhelmed

When starting to learn about DAX it can feel extremely overwhelming. It feels like there are hundreds and hundreds of concepts, and all of them are quite tricky. While it is true that the concepts in DAX are tricky, there are actually not that many of them.

Below is a map of the essential concepts a DAX student needs to master. I’ve highlighted the concepts that we either have covered or will cover in the next couple posts on the Auto Snapshot:

A good number of these concepts we’ve already hit in just this first post. Fully understanding and internalizing these concepts will take more time, but the path you have to walk here does not extend out into infinity. Between this and my last series on DAX column references we will cover most of the stacks on the left and right of the map.

Give yourself a pat on the back. You’re probably further along than you think and you’re doing great!