Variables in Power BI can be a very powerful way to enhance your DAX expressions and expand the possibilities of what DAX can do. Before Power BI supported variables, needing to use CALCULATE and the EARLIER function were cumbersome and created difficulties for data modelers. Today, those difficulties can be largely mitigated through variables as well as by using tools like DAX Studio.

Note: DAX Studio (link below) is a phenomenal tool in your DAX development arsenal. It affords you more control over your DAX code as well as allows you to see the output of your DAX as you write it. I highly recommend downloading the tool and taking a look.

Syntax of Using Variables in Power BI

There are a few different pieces of declaring and using a variable inside of a DAX formula. The first step in the formula bar is to create a new Measure and rename it to represent what you are trying to do with it.

TotalTVSales = 

After you name the measure, it’s time to declare the variable:

TotalTVSales = var VariableName = 

Now we need to define what we want the variable to hold. Variables can hold a single column, a table, or a value.

TotalTVSales = var VariableName = SUMX(FILTER(Sales,Sales[ItemType]="TV"),[Sales])

At this point, we have the variable defined to represent the data that we are looking to get. However if you run the DAX you will notice that nothing comes back except for an error. That’s because we are still missing one key piece of declaring the variable. In Power BI it’s not enough that variables have a defined function, you need to explicitly tell the measure that contains the variable to return the variable.

TotalTVSales = var VariableName = 
         SUMX(FILTER(Sales,Sales[ItemType]="TV"),[Sales])
         return VariableName

Benefits of Using Variables

The benefits of using variables in Power BI are very real. At the most basic foundational level, variables can have a great impact on performance because you can evaluate a formula once and reuse it. Otherwise you would have to recalculate the formula everywhere in your DAX that it is needed. This also does a lot to improve readability in your code, as you simply see the variable name rather than a long formula in many places.

Variables can also make debugging easier because rather than deep diving into your code to figure out where the bug is coming in, you can simply return the variable and see its output. In addition, you can apply expressions and filters to the return part of your expression. This allows you to have a common variable while also modifying its output depending on individual need.

More Links


0 Comments

Leave a Reply

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