This Article talks about the usage of DAX variables in various scenarios. Here you will learn how variables are used, benefits of using them, their relationship with filter context, use of variables as tables, optimizing calculations etc.
Introduction
Variables in DAX (Data Analysis Expressions) are a powerful feature that allows you to store intermediate calculations or values to be reused within a single measure or query. This article aims to provide a comprehensive guide to using DAX variables, including their syntax, benefits, examples, best practices, and common pitfalls. By the end of this article, you’ll have a deep understanding of how to leverage variables to design complicated logics, simplify your DAX code, improve readability, and enhance performance.
What are DAX Variables?
DAX Variables are used to store the result of an expression so that it can be reused multiple times within the same measure or calculated column. They make complex expressions easier to read and manage by breaking them down into smaller, more understandable parts. The DAX variables are not like the variables of any other coding language like python, C++, java etc. The DAX variable ‘VAR’ is a term which stores a value computed using a DAX expression and stored for further use in that measure. This Variable then actually becomes a constant as it is computed only once, and its value is reused through the entire measure. This variable cannot be used outside the measure it is a part of and the value of it will not change. There are few creative ways in which it shall become a true variable which we will discuss later in this article.
Benefits of Using Variables:
- Improved Readability: Variables make your DAX code easier to understand by giving meaningful names to complex expressions.
- Reduced Recalculation: Once a variable is calculated, its value is reused, avoiding multiple recalculations of the same expression.
- Simplification of Complex Logic: Variables allow you to break down complex logic into smaller, more manageable pieces.
- Performance Optimization: By reusing calculated values, variables can improve the performance of your DAX queries.
Syntax and Basic Usage
The syntax for defining a variable in DAX is straightforward. You use the VAR keyword to define a variable, followed by an expression to calculate its value. The RETURN keyword is then used to specify the result of the DAX expression. The VAR and RETURN keywords are case insensitive as DAX is also a case insensitive language like SQL. You can define any number of variables in a measure.
Syntax:
Measure=
var <name1> = <expression>
var <name2> = <expression>
var <name2> = <expression>
.
.
return
<expression>
Lets go through an example to show how we can make use of a variable in a measure. To do this, we will rewrite a measure using variables and we will be using adventure works database to do the same.
Example Measure:
Customer wise Average Sales Amount =
divide(sumx(Sales, Sales[Order Quantity]*Sales[Unit Price]),COUNT(Customer[CustomerKey]))
Example rewritten with variable:
Customer wise Average Sales Amount (var) =
var SalesAmount = sumx(Sales, Sales[Order Quantity]*Sales[Unit Price])
var CustomerCount = COUNT(Customer[CustomerKey])
return
divide(SalesAmount,CustomerCount) In this example, the SalesAmount and CustomerCount variables store the total sales amount, and count of customers, which are then divided using Divide function in the return statement and a result is obtained. Nevertheless, the results would be the same, except in few cases which we will discuss later in this article.
Result:
As a best practice, we encourage new developers to use variables more often that not and for a very simple reason that it helps in dividing a complex problem of designing a measure containing multiple expressions easier. You first need to think about what you need to calculate your result and put them as a variable so that you can work the variables in your measure to get the result. When designing a complicated measure, it is often required to debug a measure and reuse the elements of that measure multiple times. When we are using the same expressions in a measure multiple times, it gets calculated again and again within the same measures and becomes resource expensive, which impacts the performance of the reports. It can also become very confusing to debug a measure when is code is large. Hence variables are your friend in making your life easier with DAX expressions.
Using DAX variables for readability and debugging
Lets look at the code below for understanding how variables help in debugging the measure codes.
Original code:
ProfitMargin=
(sumx(Sales, Sales[Order Quantity]*Sales[Unit Price]) -sumx(Sales, Sales[Order Quantity]*Sales[Product Standard Cost]))/sumx(Sales, Sales[Order Quantity]*Sales[Product Standard Cost])
Modified code:
ProfitMargin Var =
VAR TotalSales = sumx(Sales, Sales[Order Quantity]*Sales[Unit Price])
VAR TotalCost = sumx(Sales, Sales[Order Quantity]*Sales[Product Standard Cost])
Var AmountDifference = TotalSales – TotalCost
Var result = DIVIDE(AmountDifference, TotalSales)
RETURN
result
In the above modified code, all the expressions are defined as variables and there is no calculation happening after return. We can easily understand what is happening in the measure at various levels instead of getting confused while seeing the original code.
the modified code also helps in debugging the measure at various levels. We can use any of the defined variable as result after ‘return’ and DAX variables would come as the result of the measure.
Debugging Example 1:
ProfitMargin Var =
VAR TotalSales = sumx(Sales, Sales[Order Quantity]*Sales[Unit Price])
VAR TotalCost = sumx(Sales, Sales[Order Quantity]*Sales[Product Standard Cost])
Var AmountDifference = TotalSales – TotalCost
Var result = DIVIDE(AmountDifference, TotalSales)
RETURN
TotalSales
Debugging Example 2:
ProfitMargin Var =
VAR TotalSales = sumx(Sales, Sales[Order Quantity]*Sales[Unit Price])
VAR TotalCost = sumx(Sales, Sales[Order Quantity]*Sales[Product Standard Cost])
Var AmountDifference = TotalSales – TotalCost
Var result = DIVIDE(AmountDifference, TotalSales)
RETURN
TotalCost
Debugging Example 3:
ProfitMargin Var =
VAR TotalSales = sumx(Sales, Sales[Order Quantity]*Sales[Unit Price])
VAR TotalCost = sumx(Sales, Sales[Order Quantity]*Sales[Product Standard Cost])
Var AmountDifference = TotalSales – TotalCost
Var result = DIVIDE(AmountDifference, TotalSales)
RETURN
AmountDifference
DAX Variables and their relationship Filter Context
When working with variables in DAX, it’s crucial to understand their behaviour in relation to filter context. Once a variable is declared and assigned a value, you cannot use a CALCULATE statement to override the filter context and obtain a new value for that variable. This is because variables represent a specific value at the time they are defined and are not recalculated thereafter.
We frequently encounter questions from DAX developers puzzled by how replacing an expression with a variable can alter the result. For example, consider the following measure:
Measure 1 =
DIVIDE (
SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] ),
CALCULATE (
SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] ),
filter(‘Product’, ‘Product'[Color] = “Black”
)))
Now let’s look at the result of Measure 1
For investigating the behaviour of DAX variable with filter context, lets make another measure as below:
Measure 2 =
var SalesAmount = SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] )
return
DIVIDE (
SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] ),
CALCULATE (
salesAmount,
filter(‘Product’, ‘Product'[Color] = “Black”
)
))
Result:
Here, SalesAmount variable is declared and it contains a part of the expression of the original measure 1. If we look at the results, we see that measure 2 is returning a constant value of 1 for all categories. This is because the DAX variable gets calculated only once when it is declared, and even if it is called in calculate function at the end of the measure, the recalculation is not happening. Instead it is advised to declare the DAX variable expressions with filter context, so that re calculation is not needed as shown in Measure 3 below.
Measure 3 =
var SalesAmount = CALCULATE (
SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] ),
filter(‘Product’, ‘Product'[Color] = “Black”
))
return
DIVIDE (
SUMX ( Sales, Sales[Order Quantity] * Sales[Unit Price] ), salesAmount
)
Result:
Looking at the above results, you will ask me that if the DAX variable is getting calculated once and returns a number, how is it changing the results for each category and returning different result? I talked about that at the beginning of this article that we can make DAX variables behave like a true variable. Well, the answer lies in the basic understanding of the filter context within visuals. For each row of the visual, a filter context gets set and the results are computed accordingly. So even if the DAX variable gets one time computation, it happens for each category.
DAX Variables can behave as tables
How? Let’s look at the below example:
Var as Table =
var SalesTable =
filter(‘Sales’, Sales[Order Quantity] = 2)
return
DIVIDE (
SUM (Sales[Sales Amount] ) , COUNT(Customer[CustomerKey]))
Here the DAX variable named SalesTable is storing all the values of the filter result where order Quantity is 2 and then the computation is happening for the same.
Best Practices for Using Variables in DAX
Naming Conventions: Use meaningful and descriptive names for your variables to make your code more readable.
Keep Variables Simple and Specific: Avoid making variables too complex. Break down complex calculations into multiple smaller variables.
Testing and Debugging with Variables: Use variables to test and debug your DAX code by temporarily returning the value of a variable to inspect its output.
Performance Considerations: Be mindful of the performance implications of using variables. In some cases, using variables can lead to better performance by reducing redundant calculations.
Common Pitfalls and How to Avoid Them
Overusing Variables: While variables are useful, overusing them can make your code unnecessarily complex. Use them judiciously.
Misunderstanding Filter Context: Be aware of how variables interact with the filter context in DAX. Once a variable is defined, its value is fixed and does not change with the filter context.
Variable Scope and Lifecycle: Understand that variables are only valid within the scope of the measure or calculated column in which they are defined.
Optimizing a Complex DAX Measure
Start with a complex DAX measure without variables, then refactor it using variables to demonstrate the improvements in readability and performance.
Initial Measure:
TotalSales = SUM(Sales[Amount])
TotalCost = SUM(Costs[Cost])
ProfitMargin = DIVIDE(TotalSales – TotalCost, TotalSales)
Refactored Measure with Variables:
VAR TotalSales = SUM(Sales[Amount])
VAR TotalCost = SUM(Costs[Cost])
RETURN DIVIDE(TotalSales – TotalCost, TotalSales)
Conclusion
DAX Variables are a powerful tool that can simplify your code, improve readability, and enhance performance. By following best practices and being mindful of common pitfalls, you can leverage variables to write more efficient and maintainable DAX expressions.
Further Resources and References