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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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:

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.

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:

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:

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.

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

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

  • 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!

  • 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

      • 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, 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.
      If you intend to use AI functions in Power BI, most of them are included in your Power BI license. for some, you might need Power BI Premium though. read this article to learn more about Power BI licensing.
      CHeers
      Reza

Leave a Reply

%d bloggers like this: