DAX Variables

What is VAR?

• VAR declares a variable to store an intermediate calculation or value.
• Variables are calculated once per evaluation context.
• Improves performance by avoiding repeated calculations.
• Enhances readability by breaking complex formulas into named steps.

Basic Syntax

Measure = 
VAR VariableName = <expression>
RETURN
    <use VariableName>

Why Use VAR for Optimization?

1. Avoid Repeated Calculations Calculate an expression once and reuse it multiple times in your formula. This saves processing time.

2. Simplify Complex Expressions Naming intermediate steps makes your code easier to understand and maintain.

3. Control Calculation Order Variables are calculated in order, so you can build on previous results step-by-step.

Example: Without VAR

Total Profit Margin % = 
DIVIDE(
    SUM(Sales[Profit]),
    SUM(Sales[Revenue]),
    0
) * 100

If you need to use SUM(Sales[Revenue]) or SUM(Sales[Profit]) multiple times, it will be recalculated each time.

Optimized Example: Using VAR

Total Profit Margin % = 
VAR TotalProfit = SUM(Sales[Profit])
VAR TotalRevenue = SUM(Sales[Revenue])
RETURN
    DIVIDE(TotalProfit, TotalRevenue, 0) * 100

• TotalProfit and TotalRevenue are calculated once.
• This improves performance and makes your formula cleaner.

More Complex Example: Using VAR with Filters

Without VAR:

Measure = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Region] = "East")
) - 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Region] = "West")
)

The filter is calculated twice.

With VAR:

Measure = 
VAR EastSales = FILTER(Sales, Sales[Region] = "East")
VAR WestSales = FILTER(Sales, Sales[Region] = "West")
RETURN
    CALCULATE(SUM(Sales[Amount]), EastSales) -
    CALCULATE(SUM(Sales[Amount]), WestSales)

• The filtered tables are created once each.
• The code is more efficient and readable.

Tips for Variable Optimization

• Store complex expressions or filters in variables.
• Use variables when the same calculation is needed multiple times.
• Break complex formulas into logical steps.
• Use variables to debug by temporarily returning them to see their values.

Summary