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

## 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)
```

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 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.

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.

## 11 thoughts on “DAX Variables: Better Readability, Consistency, and Performance in Power BI Calculations”

• Sebastian says:

Variables are awesome, especially when you use them in combination with SWITCH and TRUE (e.g. for banding).

Looking forward for more practical use cases in your blog! Thanks for your effort!

• Thanks Sebastian for your kind words 😊

• Hi Reza,
can you help me understand this part:
____________________________
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:

Asjudted Sales =
var _Sales=[Sales]
_________________________________________
I am not sure I get the point of what you are trying to explain, what they workaround is for in another words and I would like to test the thing in a measure.
Thanks

• Hi Francesco.
You can consider variables as temporary storage. when you use a measure inside another measure, for each time of using that measure, the value of that will be calculated again and again. but if you have a measure used twice or three times, variables would be better. What I meant was that you can create a variable from your measure (the code that you see in the above), and then re-use that variable multiple times. That would perform faster.
Let me know if you need more explanation
Cheers
Reza

• Got it Reza, thanks

• Alex Kim says:

Hey Reza, the Power BI version I am now using is 2.79.5768.663 64-bit (March, 2020).

I was trying to use your approach in declaring VAR from the measure I created to be used in another measure I am writing. Then, I noticed it gives me the same value over the matrix I am creating.

Here’s what I tried to do using your approach:
PreviousYear Booking =
VAR TotalBookings := [# Bookings] RETURN
CALCULATE(TotalBookings, SAMEPERIODLASTYEAR(‘Calendar'[Date]))

I didn’t get the same result as a plain measure-in-measure approach like this:
PreviousYear Booking :=
CALCULATE([# Bookings], SAMEPERIODLASTYEAR(‘Calendar'[Date]))

NB: # Bookings := COUNTA(‘Sales'[Booking ID])

As such, I am assuming it is either (1) I gotta manipulate my formula in a more complex manner to get the same result or (2) this method only words for conditionals. Please advise and tell me what went wrong. Looking forward to hearing from you.

• Hi Alex
Can you please send me your PBIX file as well, so I can have a look?
Cheers
Reza

• Rajendra says:

Hi, I would like to purchase Power BI with AI and Power BI with Python. Could you please let me know how to purchase and where to book. Please note that, I am requesting from India.

• Hi.
Power BI Licenses can be bought from Microsoft.
CHeers
Reza

• srikanth says:

i am very big fan of your the way your narrate the scenarios is very informative.

thanks