Working with variables

At first glance, writing some DAX formulas may seem difficult. This often happens due to the nesting of multiple functions. The more complex the function, the harder it is to write a readable function and eliminate all errors. In such situations, variables come to the rescue.

What are the advantages of using variables?

We can list four benefits of using variables in writing DAX calculations:
  • Improved performance;
  • Better code readability;
  • Simplified debugging;
  • Reduced complexity of our functions.

Improved Performance

Let’s look at the example below.
Zmienne Power BI
In this case, we want to calculate the year-over-year sales change. Without creating variables, I used the following measure:
SalesYoY% =
DIVIDE(
[_CurrentSales]-CALCULATE(
[_CurrentSales],
PARALLELPERIOD('Calendar'[Date],-12,MONTH)
),
CALCULATE(
[_CurrentSales],
PARALLELPERIOD('Calendar'[Date],-12,MONTH)
)
)
The results are visible in the screenshot above, but let’s see how we can write this measure using variables. The measure gives the correct result, but let’s see how we can improve it. We declare variables in DAX by using the expression VAR [Variable Name] = [DAX function]. To perform calculations after declaring a variable, our function must use the RETURN expression. Let’s look at the formula below.
SalesYoY% =
VAR CurrentSales = [_CurrentSales]
VAR PY_Sales =
CALCULATE(
[_CurrentSales],
PARALLELPERIOD('Calendar'[Date],-12,MONTH)
)
RETURN
DIVIDE(
CurrentSales - PY_Sales,
PY_Sales
)
The measure returns exactly the same result, but the time it took to perform the calculations was shorter than in the case of calculations using the standard method.
Zmienne Power BI
As seen above, refreshing the visualization that included calculations using variables took 179ms, while refreshing the visualization without variables took 210ms. That’s about 15% faster. I will add that in this example, we operate on a set that contains only 700 records, and the calculations were not so complicated. The larger the scale, the greater the savings, of course.

Better Code Readability

Comparing both measures, it’s worth noting that choosing a variable name helps understand the RETURN expression. Thanks to the use of variables, it is short and self-descriptive.

Simplified Debugging

Variables also facilitate formula debugging. To test a variable, simply enter the variable name in the RETURN expression, allowing for quick testing of code fragments.

Reduced Complexity of Our Functions

This allows new users to navigate our report more easily. Maintaining documentation related to the use of variables is also more transparent.

Summary

Using variables in creating calculation measures is considered a good practice in working with Power BI. This makes it easier not only for developers but also for other users. By using variables, calculations in Power BI can be performed faster, and the code is more transparent. The ease of debugging code improves its quality and speeds up error elimination.

Interesting article? Share further!

Leave A Comment