What is variable bi variable

DAX: Using Variables to Improve Formulas

  • 3 minutes to read

Writing and debugging DAX calculations can be challenging for data modelers. Complex computational requirements often involve writing compound or complex expressions. Compound expressions can involve the use of many nested functions and possibly the reuse of expression logic.

Using variables in DAX formulas makes it easier to do complex and efficient calculations. Variables support you in the following:

This article demonstrates the top three benefits by using a sample YoY year-over-year (YoY) sales growth measure. (The formula for year-over-year sales growth is “period sales _fewer sales” for the same period last year divided by sales for the same period last year)

Let's start with the following measure definition.

The measure produces the correct result, but now let's see how it can be further improved.

Improve performance

Notice that in the formula, the expression for the calculation is repeated for the same period in the past year. This formula is inefficient because Power BI has to evaluate the same expression twice. The measure definition can be made more efficient with the help of a variable.

The following measure definition shows an improvement. An expression is used to represent a variable named SalesPriorYear assign the result for "same period last year". The variable is then used twice in the "RETURN" expression.

The measure continues to produce the correct result in about half the query time.

Improve readability

Note that selecting the variable name in the previous measure definition makes the expression “RETURN” easier to understand. The expression is short and self-explanatory.

Simplify debugging

Variables can also help when debugging a formula. To test an expression associated with a variable, temporarily rewrite the RETURN expression to return the variable.

The following measure definition only gives the variable SalesPriorYear back. Notice how the intended phrase "RETURN" is commented out. This technique allows you to easily recover the expression after you have finished debugging.

Reduce complexity

In previous versions of DAX, variables were not yet supported. Complex expressions that introduced new filters had to use the DAX functions EARLIER or EARLIEST to refer to outer filter contexts. Unfortunately, according to data modelers, these features have been difficult to understand and use.

Variables are always evaluated outside of the filters that the expression "RETURN" applies. If you use a variable within a changed filter context, the same result is achieved as with the "EARLIEST" function. Therefore, the use of the “EARLIER” or “EARLIEST” functions can be avoided. This means you can now write formulas that are less complex and easier to understand.

Note the following definition of a computed column, that of the table Subcategory was added. It evaluates based on the values ​​in the column Subcategory Sales (Sales sub-category), select a rank for each product sub-category.

The "EARLIER" function is used to access the value in the column Subcategory Sales (Sales subcategory) im current line context to refer.

The definition of calculated columns can be improved by using a variable instead of the "EARLIER" function. The variable CurrentSubcategorySales saves the value of the column Subcategory Sales (Sales sub-category) in the current line context, and the expression "RETURN" uses it in a changed filter context.

Next Steps

For more information about this article, see the following resources: