DAX Deep Dive: Using DAX Variables

In my last post on the CALCULATE() function, I used variables to improve my DAX measure. Today we’re going deeper into how to use DAX variables One of the big ways to take your DAX to the next level is by using variables in your DAX expressions. DAX variables are comparable to using common table expressions (CTEs) in your SQL code. They provide a way to simplify complex formulas, improve code readability, and make it easier to maintain and update your reports.

In Power BI, variables are defined using the VAR keyword within DAX (Data Analysis Expressions) formulas. They can store various types of data, including numbers, text, tables, and even entire expressions. Variables are scoped to the measure or calculated column in which they are defined and are not accessible outside of that context.

Benefits of Using DAX Variables

Key benefits of using variables in Power BI include:

1. Improved readability: By breaking down complex calculations into smaller, named components, variables make formulas easier to understand and maintain.

2. Reduced redundancy: Variables allow you to compute a value once and reuse it multiple times within a formula, reducing repetition and potential errors.

3. Enhanced performance: In some cases, using variables can improve query performance by avoiding redundant calculations.

4. Easier debugging: Variables make it simpler to isolate and test specific parts of a complex calculation.

DAX Variable Grammar and Structure

Using DAX variables requires a slightly modified structure to typical DAX expressions. Each variable is called out specifically using the VAR keyword. A measure/calculated column also needs the RETURN keyword to tell Power BI what part of the measure is being displayed.

Here’s the generalized format for using variables in DAX:

DAX_Var_Example = 

VAR myVar1 = XXX
VAR myVar2 = YYY
VAR myVar3 = ZZZ

RETURN (myVar1-myVar2)/myVar3

The VAR Keyword

VAR uses a named variable to store the result of an expression. The variable can then be passed into other expressions as an argument. Variables specified in the example above are:

• myVar1

• myVar2

• myVar3

The RETURN keyword

Return tells your DAX expression what is to be returned. You can do all sorts of fancy stuff with Variables, but the variables are scoped to the measure/calculated column. Without RETURN you have no way of accessing the results of any of your expressions in the measure.

DAX Variable Example

The following is a measure that will give you the average difference in Salary between India and the USA. I’m still working with the dummy data that I created in my Row Level Security Primer.

AvgSalaryDifference = 
VAR AvgSalUSA = CALCULATE(
    AVERAGE(Employment[Salary]), //Average Salary
    ALL(Employment),             //Remove all filters (including slicers)
    Geography[Country] = "USA"   //Add filter for country = "USA"
)
VAR AvgSalIndia = CALCULATE(
    AVERAGE(Employment[Salary]),
    ALL(Employment),
    Geography[Country] = "India"
)
VAR PercentDifference = DIVIDE(
    AvgSalUSA-AvgSalIndia,
    AvgSalUSA
)
RETURN PercentDifference

You can see that there are three variables here, AvgSalUSA, AvgSalIndia, and PercentDifference. I’m using the CALCULATE() function, which I went into in depth in my previous post. I’m also using ALL() to remove any filters that are put onto this measure by the slicers. Finally I’m using DIVIDE() instead of “/” to clean things up a bit and RETURN to return my final value.

Here’s the final output:

The final value is in percent because I selected Percentage from formatting at the top of the page. This measure shows that the difference in average salaries between the US and India is ~50%.

Conclusion

Variables are nice in that they allow you to simplify your DAX measures, making them readable and easy to troubleshoot. One of my least favorite things is tracking measure dependencies, going two, three , four or more measures deep to understand how something is working. Using variables to make each measure self-contained will help you in the long run with the maintenance of your Power BI Reports. DAX variables are an important part of that behavior.