Tired of writing long, clunky DAX formulas that are a nightmare to read? There’s a simple fix — variables.
What are Variables?
Variables work as placeholders for expressions that you can refer to later on within the same DAX calculation.
They work by storing the result of an expression as a named variable which you can then use as an argument within other expressions.
These are extremely useful for making formulas easy to understand and break down when troubleshooting.
Syntax
In essence, you state the name of your variable and what expression you want the variable to represent using the VAR function.
Afterwards, you use the RETURN function which allows you to create another DAX statement which can now include the same of your variable, as opposed to including the entire expression. It is similar to referring to a Measure in this case, but the variable is not stored anywhere and can only be used within the same formula (in other words, you cannot refer to a variable created in a different formula/measure/table/calculated field).
Example
Using the Superstore dataset, I want to find the average profit per order.
To do this, I have created a measure and have written the following:
- Created two variables using VAR - one for TotalSales and one for TotalProfit - note how you can include multiple variables in a formula.
- Using RETURN, I have included the names of my variables in the return statement, so my DAX knows what expressions they are referring to.
Overall, it is much easier to understand what the return statement is doing by using the names of the variables. As you can give the variables any name (and can include spaces) you can make your expressions really clear.
Advantages of using variables
- Reduces complexity
- Improves readability
- Simplifies debugging process
- Improves performance - not only is it easier for us to understand DAX expressions this way, but it is also faster as it can save Power BI from reading the same expression multiple times
I would recommend the following website to see more use cases for variables.