In the following three posts I’m going to focus on a topic that you run into every day but probably don’t think much about. It’s pretty important though and worth exploring thoroughly.

Whenever you create a column reference in DAX like:

Sale[Qty]

It can actually have one of three kinds of meanings depending on *where* you use it:

1. Current Row Reference
2. Model Column Reference
3. Table Column Reference (also called: Temp Column Reference)

While understanding all three is valuable, it’s only the first two that are really, really important for new DAX author to understand and tell apart. Those are the ones that I’m going to focus on in this series.

My guess is that lots of folks have had an intuitive sense of how they work, but couldn’t articulate the differences, and might not even be confident that there are differences.

With that in mind, I wanted to explain what the differences are, and how you can spot one vs the other in your own code.

(As is always the case, it’s not actually important that you use the exact same language that I do. If you understand the ideas you can use whatever terms you like or no terms at all.)

This is a tricky set of posts, but it’s an important one. It might take a couple passes to fully get, but upon doing so you will understand some of the most basic rules of DAX that can elude people for years (not kidding here).

Table Vision

The single most important skill in DAX is Table Vision; that is to say, when you write code, you are able to envision what tables you are asking DAX to create and what you are then asking DAX to do with those tables.

Envision does not mean describe. Looking at code as hearing the sentence “that’s a table that has all my customer numbers in it” does not count. Looking at that same code and (in your minds eye) actually seeing a fuzzy gray rectangle that is thin but tall and feels like it has all the customer numbers in it is what Table Vision is all about.

To develop good Table Vision, when you see a column reference written in code, you need to know the correct way to envision it in your head. Since a column reference can mean two different *kinds* of things, you need to be able to tell which kind a particular reference is, otherwise the mental picture you are drawing in your head will be wrong, and writing DAX will be miserable.

Said otherwise, a column reference can mean different things depending on where it’s used; you have to know which meaning is correct so you can draw the right image in your head.

DAX is a simple game with simple rules. In this article I want to give you a clear understanding of a small section of the rules of that tends to confuse folks.

So here, well before it makes any sense to you, let’s start with the rule that we will be explaining:

Any column reference in DAX is always a Current Row Reference unless it is being made as an argument of functions that requires one of the other types (Model Column Reference or Table Column Reference).

So with that target set, let’s starting building up these ideas.

The Current Row Reference

In DAX, all the X functions (SUMX, MAXX, AVERAGEX, etc) fall into the category of Iterators. There are other Iterator functions, but the X functions are the most commonly used.

So what do we need to know about Iterators?

All Iterators in DAX, by definition, require a Per Row Formula as an argument. Even if you’ve never heard this term before, I guarantee you’ve written several and maybe several hundred of these.

The Per Row Formula is the, well, the formula that we are asking DAX to run once-per-row of some Temp Table. For all basic Iterators, the Per Row Formula is entered into argument 2. That means whenever you are typing something into argument 2 of an Iterator, congratulations, you are writing a Per Row Formula.

(I often call these Per Row Formulas “sub formulas” because they are a formula within the larger formula of our measures. In this post I’ll just stick with “formula”.)

Let’s imagine a very simple DAX measure:

Total Sales = 
SUMX(
    Sale,
    Sale[Qty] * Sale[Price]
)

Argument 2 of SUMX is the Per Row Formula. Again, this is the formula that we are asking DAX to run once per row for whatever Temp Table we specify in Argument 1.

So we’re telling DAX:

“For each row, run the formula: Sale[Qty] * Sale[Price] “

The references to Sale[Qty] and Sale[Price] in the Per Row Formula are both examples of Current Row References. Which is to say, they refer to a value within “this row”.

I put “this row” in quotation marks here because the Per Row Formula, as the name suggests, is run several times (once per row). That means I can’t tell you what number the reference Sale[Qty] returns unless you tell me which row we’re currently running the formula in.

To make this a bit more concrete, let’s start by just thinking about the sample Data Model we’re working with and what the little section of the Power BI report looks like:

Our Data Model has a Sale table, a Dish table, and a many to one relationship from Sale[Dish] to Dish[ID]. In the Power BI report, the user has sliced down to lunch, and when they did, the Total Sales measure returns $62.

What was the measure again?

Total Sales = 
SUMX(
    Sale,
    Sale[Qty] * Sale[Price]
)

That’s right. OK.

Now let’s sketch out what that measure does to come up with $62:

At the very top left is the Filter Context, which has all the filters that affect what our final number will be. Because the user selected “Lunch” on the slicer above, the Filter Context has one filter for Lunch.

The reference to Sale (the one with a camera icon) creates a Temp Table with just the lunch rows from Sale. It’s got some weird extra columns, but let’s ignore those for now. This is the Temp Table that gets handed to SUMX as argument 1.

In the SUMX area (between the blue curly braces), the Per Row Formula of “Sale[Qty] * Sale[Price]” gets run four separate times, once for each row of that Temp Table.

For the top row, Sale[Qty] is 2 and Sale[Price] is $10.
Multiply those together and get $20.

For the second row, Sale[Qty] is 1 and Sale[Price] is $8.
Multiply those together and get $8.

For the third row, Sale[Qty] is 1 and Sale[Price] is $10.
Multiply those together and get $10.

For the last row, Sale[Qty] is 3 and Sale[Price] is $8.
Multiply those together and get $24.

Having finished running the Per Row Formula for all the rows, as this is the SUMX function, DAX takes the results ($20, $8, $10, $24) and sums them up get $62, the final answer.

Good, let’s rewind a second and focus on the top row.

One Name, Two Things

It seems so blindingly obvious that within the top row, the reference Sale[Qty] refers to the value for “that row” that it’s tempting to not point out some potential ambiguity.

Up in the Data Model, there is also a column with the exact same name of Sale[Qty]. That means there are two things both with the name Sale[Qty], a number in the current row and a column in the Data Model.

So if we’re considering the reference to Sale[Qty] in the red box, then both the number in the green box and the column in the purple box are both things with the name Sale[Qty].

Which thing should DAX get when asked for Sale[Qty]?

The short answer is DAX is simply smart enough to know that even though two things with the same name exist, Sale[Qty] probably refers to the number in the current row, not the whole column in the Data Model.

The slightly longer answer is that any column reference is assumed to be pointing to something in the current row (a Current Row Reference) unless it appears in a function that is tasked with specifically looking somewhere other than the current row for things (like the Data Model).

OK, so how does “looking somewhere else” work? That’s the top of part 2.

Post Script: You’ve Learned More Than You Think

If you feel overwhelmed at this point, that is reasonable. We’ve introduced a lot of concepts and are about to introduce some more.

Take heart though, the number of basic concepts you have to understand in DAX is not limitless. Between this and the next 2 posts we’re actually going to cover a good number of them. Here’s a map of the key concepts new DAX authors need to learn along with which ones we just covered (dark yellow) and which we are about to cover (light yellow):

We’re not going to master these concepts in this series, but we will give you the basics. So if you do the work to read and reread these blogs; you will walk away knowing a whole lot of important stuff about DAX.