In this video, we’ll learn about variables and how to use them to improve calculations in DAX.
DAX code can become complex pretty quickly. Therefore, the use of variables can help. Variables in Power BI can help to improve performance, readability, simplify debugging and reduce complexity of calculations.
First, let’s take a look at how to define variables in DAX. The syntax is quite simple. To start using variables, follow two steps:
- Create a measure and add the variables, by writing VAR var_name = [Valid DAX formula]. You can have as many variables as needed in a single expression, and each one has its own VAR definition.
- Return the calculation for the set variable by writing RETURN [Another DAX formula with the variable used as part of the calculation.
Below you can see the DAX formulas used in our video example. Notice the difference in writing the code and how using the variables makes the code a lot easier to read:
YoY Growth % = DIVIDE( [Total Sales] – CALCULATE( [Total Sales],
SAMEPERIODLASTYEAR( Dates[Date])), CALCULATE( [Total Sales],
SAMEPERIODLASTYEAR( Dates[Date])) )
YoY Growth % VAR =
Var SalesLY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
Var Difference = [Total Sales] – SalesLY
Return DIVIDE( Difference, SalesLY)
The example shows how the two variables make the code easier to read. Finally, it will be easier for someone else, who didn’t write the code, maintain it over time.