Power BI introduced variables to DAX expressions years ago – and forever made writing code easier and cleaner! The format is extraordinarily easy and I’ve come to adopt variable use in nearly all of my Power BI projects.
However, every so often, I will find that a variable just doesn’t work in a DAX expression. When this happens, I find myself asking….
“Why isn’t the variable working correctly here?”
“Why does the formula work correctly when I use a measure but not when I use a variable of the exact same measure?”
Of course the answer to these questions is in the title of this post, but I’ve never really taken the time to refresh exactly why this happens.
In this post – I hope to clear this up (for myself) once and for all!
Let’s dig into variables using an overly simplistic model with two tables: A dimension table with 3 colors (Green, Red, & Yellow) and a fact table of color sales:
We will start with a simple measure called [Total Sales]
Total Sales =
SUM ( ColorSales[Sales] )
This will be home base. We need a few more formulas to help set the stage. The next formula returns the [Total Sales] of Green only.
Total Sales of Green =
CALCULATE (
[Total Sales],
Color[Color] = "Green"
)
These measures should (hopefully) make sense.
Now to illustrate variable issues – let’s reconstruct these two measures using variables.
Total Sales using a Variable =
VAR TotalSales = [Total Sales]
RETURN
TotalSales
And one more…..
Total Sales of Green Using a Variable =
VAR TotalSales = [Total Sales]
RETURN
CALCULATE (
TotalSales,
Color[Color] = "Green"
)
Now let’s focus on the second and last measures. Under normal circumstances, we EXPECT these two measures to give us the same answer (all 17’s), but they don’t. The last measure is clearly wrong….
This is at the heart of every headache I’ve had with when and how to use variables correctly. So let’s dive into why this is happening.
When the formula engine calculates a DAX measure – it does so using a specific sequence of EVALUATION steps. If you’ve ever read a book or taken a class with Rob Collie – you’ve probably seen these steps listed out:
- Determine coordinates of current measure cell.
- If Calculate() is used, apply new filter context
- Apply the coordinates of the filter context to each respective (dimension) table
- Filters follow the relationship(s) back to the (fact) table
- Evaluate the Arithmetic
- Return Result
These steps occur – every time – for every cell – in every measure we use. It’s pretty remarkable considering just how quickly this all happens when we plug a measure into a table. It’s easy to forget.
When we include a variable in our DAX (as the title of this post reiterates) – the variable is evaluated one time and then the value becomes immutable, meaning we can no longer impact or change the value. It’s a constant!
Now here’s the important part: Where the variable is declared in the measure impacts how it fits into this order. Let’s review why this matters for our measure that is calculating differently than we intended.
In our measure – the variable is declared before and outside of the CALCULATE. So in the EVALUATION steps, it would fall somewhere between #1 and #2.
This means that our [Total Sales] variable is evaluated using the ORIGINAL COORDINATES – and then the value is locked in place. This is why our values for the [Total Sales of Green Using a Variable] measure are EXACTLY the same as the values for the original [Total Sales] measure. We essentially called the variable too early – and locked the values at that original filter context.
If this is true, we should be able to test a solution by simply shifting where in the formula we declare this variable. If it shows up after the CALCULATE – we have essentially shifted it a step down in the evaluation order ( and our variable will now be impacted by the changed filter context that CALCULATE introduces.
The revised formula looks like this.
Total Sales of Green Using a Variable =
CALCULATE (
VAR TotalSales = [Total Sales]
RETURN
TotalSales,
Color[Color] = "Green"
)
And the result – gives us the exact values we would expect:
Ok – so it may not be practical to start declaring variables inside calculate when simply using the original measure would suffice – but I love these simple examples to help hammer home the mechanics of why something does or doesn’t work. A similar example could be done if we evaluated a variable inside an iterator function as well (SUMX).
In both cases – context is everything.
Wise Data Decisions 