Variables are used in DAX to optimize the DAX code. The DAX engine evaluates the variables only once, making the code faster and more readable. Using variables in DAX helps you:
- Improve code readability.
- Improves code performance.
- Reduces complexity.
- Simplifies code.
Let’s look at the structure of the code.
- VAR key word is used to define the variable.
- Name refers to the name of the variable.
Please note that you can’t use space in variable names.
- The equal sign links variable name to the value.
- The expression that is evaluated and the resultant value is stored in variable name.
- RETURN is the key word for returning the value.
- The returned value of variable or results of evaluated expression.
Let’s see how a variable works:
- Right click on the All Measures
- Click on New Measure.
Measure tools open.
- Type the following expression in the formula bar.
Notice that the intellesence detects the variables and identifies them by the ‘xy’ symbol.
Features of a Variable
Let’s look at some of the features of a variable:
- Variables always have ‘VAR’ and ‘RETURN’ keyword.
- Variables can be used for scaler values or for tables.
- Variables can be defined by other variables.
- Variables can be defined anywhere in the DAX expression.
- They are evaluated only once.
- The variables are executed in the initial filter and row contexts.
- Once the variable’s value is evaluated their value does not change. This means they act like constants in the regular programming language.
Conditional Computations using Variables
Conditional computations can be faster using the variables because the variables are evaluated only once. Variables are very useful to avoid repeating subexpressions in the code.
To understand how variables are used to avoid repeating subexpressions:
- Right click on the All-Measures
- Click on New Measure.
Measure tools open.
- Type the following expression in the formula bar and press Enter.
In this Expression:
- The TotalQuantity variable stores the sum of all the values in the order quantity column.
- The value of the variable is evaluated and if it is greater than 1000, this value is multiplied by 0.95 and returned otherwise the value in the variable is multiplied by 1.25 and returned.
- Click on the matrix visual in the visualization pane.
- Drag and drop Category, Subcategory and Model from the Products table to the rows.
- Drag and drop Order Quantity from the Sales table to the Values
- Drag and drop Testing Variables (CM) from the All Measures table to the Values.
Notice that the value for order quantity is evaluated only once and that value is used for further calculations in IF(). If the variables were not used here, the same expression needed to be evaluated multiple times.
Conclusion:
Variables in DAX increase the code readability, simplify code and improve code performance. The structure of a variable consists of a variable name, an expression, and a return statement. They are characterized by the ‘xy’ symbol and are calculated only once during a calculation. This blog provides a brief introduction to variables in DAX along with an example to practice the concept.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses:
