Power BI Dax Variables Are Constant: What Does This Mean?

Whenever you’ve heard an explanation of how to use Power BI DAX variables, you may have heard the phrase, “variables are constants”. It seems simple, but there’s a fair amount of nuance and complexity in these words. In this blog, I’ll walk you through what this phrase means, and the implications for how your measures are calculated. Understanding these concepts is essential to ensuring that your measures produce the desired results. You can watch the full video of this tutorial at the bottom of this blog.

A variable within DAX can really be any valid DAX expression. It can be a value, a calculation, a measure, or a variable that represents a table, another variable, or some combination of those things.

There are two parts to a variable. There’s the VAR declaration where you initialize the variable; you give it a name and then a value. The other is the RETURN statement, which then calls at least one of the variables that you declare. It’s not like parenthesis where you need to have one RETURN statement for each variable, but you need to have at least one RETURN statement if you declared any variables.

The key here is that, as I said before, variables are constant. What that means is that once variables have been assigned to a value, that value cannot change prior to the RETURN statement.

Table of Contents

Applying Power BI DAX Variables Into Measures

Let’s take a look at a simple example where I’ve got Countries and Total Sales. The Total Sales measure is just our most basic measure, which is the SUM of Line Sales within the Sales table.

Let’s say, we want to get the Total Sales for the year 2020. In this data set, we’ve got sales for 2019, 2000, and 2021. To get the sales for 2020, we have to apply a very simple filter condition, which is CALCULATE. So, we change our filter context. We have our Total Sales measure, and then our year is 2020.

If we drop that measure into the table, we get exactly what we expect, which is the right number for 2020.

Now if we want to apply variables to this, here’s a slightly revised version of the previous measure, where I created a variable called TotSales, and just set that equal to our Total Sales measure.

If we think about it from the standpoint of algebraic substitution, if we just substitute what was previously our Total Sales measure here for TotSales, which is equal to that Total Sales measure, you will think that it might be the same thing and produce the same result. But if we take a look, and we drop that measure into our table, we’ll see that it does not produce the same result.

The variable TotSales is declared, and that gets the value of total sales. Say we’re on the Moldova row, and that variable gets the value of 2.35 million. Now, remember that the value can’t change until after the RETURN statement is called. So, when we go down to calculate our measure in the RESULT variable, what we’ve got is TotSales, which is a constant at 2.35 million.

And then, we apply our filter context to it, Dates[Year] equals 2020, but it doesn’t matter because that constant can’t change. So, we could say Dates[Year] equals 2019, 2021, and so on, it’s still going to be the same 2.35 million because that TotSales is now locked in as a constant until the RETURN.

And when it returns, then we go to the next row, and the variable picks up the value of Netherlands (4.4 million). The process repeats itself, locking that in as a constant until the RETURN statement. What we get is exactly what we have in the Total Sales measure, not in the measure that filters down to 2020.

And so, you might be thinking that that doesn’t actually seem very useful. In this context, it’s not. This is actually a common mistake when people start initially using variables. They put the variable in the first expression portion of the CALCULATE statement. That is typically not going to give the result they’re looking for because it locks that in as a constant value.

Using Power BI DAX Variables Within Iterators

Variables really shine when you put them within iterators. I’m going to talk you through this case, which is a previous value one. We’re looking for a date, and then a previous value subject to a number of conditions.

Remember the way you used to have to do this before variables came to DAX was through a function called EARLIER. But now, variables actually work so well in this context. With variables, it’s highly recommended not ever use that EARLIER function anymore.

So, let’s take a look at a more complex example, where we’ve got variables in the iterator portion rather than in the expression portion. Below is actually a case from a question that came up in the Enterprise DNA forum wherein the member wanted to look at each date within a range, and then come up with the first prior date that was not a weekend or a holiday.

I want to focus particularly on Tuesday, October 15th as the prime example. The desired result was to look at that date. Monday was Columbus Day, so it was a holiday, then the next previous days were Sunday and Saturday. And so, the date that we wanted to get was October 11^th, which was the Friday.

If you look at the Previous Day/No weekend/No Holiday column for that, Tuesday was the 11th. The same thing was true for Monday, Sunday, and Saturday. That’s what we want to get as the result.

Beside the table above is the measure that we used for this case scenario. We declared a variable that was selected date (VAR SelDate), and that was just SELECTEDVALUE of the Date. It would pick up October 15th in our variable and then hold that as a constant until the RETURN statement.

Then, we evaluate the DAX outside in. If we look at the filter condition, it’s removing ALL the filters on Dates, and then it’s setting a series of additional filters. It’s taking that constant selected date, October 15^th, and it’s saying, give me all the dates prior to that. And then, of those dates make sure that none of those dates are a Saturday, Sunday, or a holiday. Then, it takes the MAX of those dates that were filtered.

You can see here that having selected data as a constant is kind of a stake in the ground upon which all the other dates are evaluated either before or after.

We’ve got our selected date, and then we’re filtering everything that’s prior to that. We’re taking the max of that filtered table. And so, what we get is the max on the 15th when we take out all those other conditions is that Friday the 11th. Then, if we go down to the RETURN statement, it starts over with the next day, the 16th. It just continues to iterate through the table until it gets to the end of the data.

As you can see in this case, when we put variables into the iterator portion rather than the expression portion, it works exactly as we planned. It takes advantage of the fact that variables are constants.

Conclusion

In this blog, I’ve shown you how to apply Power BI DAX variables in a measure and how it works when variables are placed in the iterator portion of a measure.

I hope that these examples explain the phrase “variables are constant,” giving you some insight into perhaps why your measures seem to work and sometimes they do not. I hope this provides you with some general understanding of what that important concept means.

All the best!

Brian