DAX Variables: Better Readability, Consistency, and Performance in Power BI Calculations

Have you ever had a scenario that you need to use part of your calculation multiple times? You might go and create a table or column for that and then re-use it. However, there are times that you just need that calculation to be re-used multiple times within one place. DAX variables can help you with that. DAX variables are also helpful to make the performance of your calculation better. In this article, I will explain what is DAX variable, scenarios of using it, and how it can be better for your Power BI calculations. If you want to learn more about Power BI, read Power BI from Rookie to Rock Star.

Re-Using Part of the Code

It happens sometimes, that you need to re-use part of the code. Consider the example below:

Adjusted Budget = 
IF(
    SUMX(
        FactInternetSales,
        FactInternetSales[SalesAmount]-FactInternetSales[TotalProductCost]
    )
    >
    SUMX(
        FactInternetSales,
        FactInternetSales[UnitPrice]*FactInternetSales[ExtendedAmount]
        ),
    SUMX(
        FactInternetSales,
        FactInternetSales[SalesAmount]-FactInternetSales[TotalProductCost]
    ),
    SUMX(
        FactInternetSales,
        FactInternetSales[UnitPrice]*FactInternetSales[ExtendedAmount]
        )
)

The expression above is hard to read, and also has some repetitive sections, let me mark them for you for better understanding:

We have to main parts in the expression above: A and B. Each of those is doing a calculation. Now, with the markings above, reading the expression is much simpler. The whole expression means this:

=IF(A>B, A, B)

All the above expression is saying is that if A is bigger than B, then return A, otherwise B. Now it is much simpler to read it because we split the repetitive parts into sections. That is what exactly the DAX variable is for.

DAX Variables

You can define a DAX variable with VAR (not case-sensitive), and then re-use it as many times as you want through the same expression. Here is for example, how I define a variable for A:

Adjusted Budget = 
var A=SUMX(
        FactInternetSales,
        FactInternetSales[SalesAmount]-FactInternetSales[TotalProductCost]
    )

Variables can be defined in any of the calculation objects: Column, Table, or Measure

The expression above is not yet a complete one, and if you try something like that you will get an error. defining the variable is part of the operation, the other part is to return something. that is what we do using the RETURN keyword.

Adjusted Budget = 
var A=SUMX(
        FactInternetSales,
        FactInternetSales[SalesAmount]-FactInternetSales[TotalProductCost]
    )
return A

This expression just defines a variable and returns it. We don’t really use the part of the expression defined within variable more than once, but still ok.

You can define more variables by adding more VAR to the statement. Here is what our expression looks like using the variables:

Adjusted Budget = 
var A=SUMX(
        FactInternetSales,
        FactInternetSales[SalesAmount]-FactInternetSales[TotalProductCost]
    )
var B=SUMX(
        FactInternetSales,
        FactInternetSales[UnitPrice]*FactInternetSales[ExtendedAmount]
        )
return
IF(A>B,A,B)

Variable makes your code more readable and more consistent

The example above clearly shows how variables can make your code more readable. Instead of having an expression which is long and uses a lot of repetitive parts, you can define the repetitive part of the expression once, and re-use it as many times as you want. when you define a part of the expression once and re-use it multiple times, your code will become more consistent too, and easier to maintain.

Variables are better for performance

Variables are not just good for readability, they are also good for performance. In the expression below: the calculation for part A has been done twice, same for part B:

However, if you define the part as a variable, then calculation happens once, the result stored in the variable, and re-used multiple times. This would perform much faster than re-calculating it.

Anything can be stored in a variable: Table or Value

Another good thing about the variable is that you can even store a table in a variable. Like below:

var _allSalesTable=ALL(FactInternetSales)
var _totalmargin=SUMX(
            _allSalesTable,
            FactInternetSales[SalesAmount]-FactInternetSales[TotalProductCost]
)

As you can see, in this expression ALL of the FactInternetSales stored in a variable. This is a whole table stored in a variable and can be used in other places.

Variables can be created inline: Scope

a variable definition should not be always the start of your DAX code, or even in the main part. it can be somewhere inline in another function. The expression below shows another way of writing for the last expression you have seen in the above;

This also means that variables have a scope, in which they operate. if you define the variable within the SUMX expression, then the variable cannot be used outside of that part. if you define the variable at the beginning of the expression in the main script, then it can be used anywhere in the same expression.

Variables vs. Measures

One of the questions you might have now is that; sometimes, for re-using an expression, you create a measure for it, and then use that measure in the next expression. How that is different from a variable?

Measures are global, Variables are local

Measures are global and can be used in any other measures in the same Power BI file. However, variables are local, can be used only in the expression in which they are defined.

Variables are better for performance

Because of the nature of the way that variable is calculated, it would perform faster than using a measure instead of that. If you define a variable, the calculation for that variable is done once. However, if you create measures, and then re-use measures multiple times in an expression, that measure calculation is done multiple times.

a workaround for this is to create a measure, and then define a variable to materialize that measure in your expression. like in the below example, I have created a variable from the Sales measure:

Variables can store more than just a single value, they can store a table

Another benefit of using variable is that you can store a whole table in a variable. In a measure, you can just return one single value.

When Should You create a variable?

You might ask: OK, I understand what variable is, but I cannot understand a scenario to use it, or where in my DAX calculations should I change and add a variable? That is a very good question. Here are my rules of thumb for creating variables:

• If you use a part of your expression more than once
• If your expression is hard to read
• if the calculation is slow, and within that expression, you re-use part of the expression (note that this might be somewhere in the hierarchy of the measure, if you are pointing to a measure and that is using another measure, etc. somewhere down in the hierarchy, you might have used a section more than once)

Summary

Variables, in my opinion, are the hidden gem of DAX expressions in Power BI and any other tools that use DAX. Using variables, you will have a better code, your code would be more readable, more consistent, easier to maintain, and most importantly; performs faster. Now your action is to go and use variables within your code and tell me about your experience down below in the comments.

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.