Caution When Using Variables in DAX and Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

I have written another blog post about variables previously and explained how useful variables are. Variables in DAX are helpful in both readability and also the performance of your code. However, there are scenarios that you have to be careful when you use variables. Because variables are stored, they might return a result that is different from if you had that definition in a measure. Let’s see an example in this blog post.

Variables in DAX

You can define a DAX variable using VAR statement, and then use it in a RETURN statement, or even in another variable through that expression. Here, for example, you can see a use case of the variable:

The above is an example of the “right” usage of the variable. Variable can be used mistakenly in a wrong situation though. Let’s see an example. If you want to learn what are variables and how they can make your DAX expressions better, read my blog article here.

Calculating a Value Before Filtering

One of the most common mistakes for using variables is in a scenario that you calculate the value before the custom filter applied. Let’s assume you want to calculate the same period last year sales amount, the calculation normally is like below: (If you like to learn how to calculate the same period last year value, read my blog post here)

For the expression above, you don’t need a variable, but let’s say even for that expression if you decided to create a variable for the expression part of it, it looks like below;

As you can see in the below screenshot, the result is wrong! The result is similar to the sales amount of each month rather than the sale of the same period last year!

Why?

In DAX, variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.

This means that when we create the variable as VAR _sales=SUM(FactInternetSales[SalesAmount]), the expression runs on the filter context of the visual, which is the month in which the calculation is evaluated. As a result. for each month, the value of _sales would be the sum of the SaleAmount column in that month, then the rest of the expression is using the stored result from that point onward.

Because the value of the variable is stored, it is better that you calculate it considering all the filters that you want to apply.

In this case, the context of the filter which we are trying to add is the SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey].[Date]), and we want the variable to be calculated using this filter context.

So calculating the variable in this scenario before the CALCULATE statement, make it wrong. You either have to not use a variable here for this scenario, or use it in the context of that filter, which is possible with the change in SCOPE of the variable;

This expression is using the variable inside the context that it should be calculated, and as a result, it returns the correct output;

Another Example

The example you have seen is more like a “learning” example because you won’t create a variable usually for such a simple statement. I used that example to explain to you what is the problem, and how to solve it. Now, here is another example, which makes sense more;

Then this would be the wrong usage of variables for it:

Wrong Use of Variables

The expression below returns a different result from the above expression because all the variable values are calculated outside of the CALCULATE expression and the filter of SAMEPERIODLASTYEAR.

The Right Way

The expression below, however, is correct, because the variables are used in the scope of the context of the filter needed to be applied:

Here is the result and comparison of the two different expressions:

Summary

You might say that there are other ways of writing this expression without the need for variables, and you are right. The purpose of this post, however, was to teach you, that although variables can be used anywhere, however, their result might not be always the same. Because they are evaluated in the context in which they are written. The scope that you write the variable is important and should be used cautiously.

Video

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

2 thoughts on “Caution When Using Variables in DAX and Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *