Variables in DAX in Power BI

In DAX (Data Analysis Expressions) used in Power BI, variables help simplify our expressions, improve readability, and optimize performance by storing intermediate results that can be reused multiple times within the same formula. Variables make your code easier to read, debug, and maintain.

Syntax of Variables in DAX

To define a variable in DAX, use the VAR keyword followed by the variable name and an expression to assign its value. The result is returned using the RETURN keyword.

DAX

VAR VariableName = Expression
RETURN Final Expression

Example: Basic Use of Variables

Without Variables:

DAX

SalesAmountWithDiscount =
    SUM(Sales[Quantity] * Sales[Price]) * (1 - [DiscountRate])

With Variables:

DAX

SalesAmountWithDiscount =
VAR TotalSales =
    SUM(Sales[Quantity] * Sales[Price])
VAR Discount =
    [DiscountRate]
RETURN
    TotalSales * (1 - Discount)

Variable Evaluation Order

In DAX, variables are evaluated in the order they are declared, but only when they are actually used (a concept known as lazy evaluation). Let’s break that down:

How Variable Evaluation Works in DAX

Variables are not calculated immediately when declared.
DAX will evaluate each variable only when it's needed, and only once.
This lazy evaluation is what makes variables helpful for performance optimization.

Example: Evaluation Order

DAX

Result =
VAR SalesAmt = SUM(Sales[Amount])
VAR Bonus = SalesAmt * 0.1
VAR Penalty = SalesAmt * 0.05
RETURN
    IF(SalesAmt > 100000, Bonus, Penalty)

Evaluation Order:

SalesAmt is evaluated first because both Bonus and Penalty use it.
Then only one of Bonus or Penalty is evaluated depending on the condition.
This makes the formula efficient — unnecessary parts aren't calculated!

Redefine Variables

We cannot redefine variables in the dax function.

Example: Incorrect (Trying to Redefine a Variable — Not Allowed)

Let’s say we want to calculate bonus based on sales tiers.

DAX

BonusCalc =
VAR Bonus = 0
VAR SalesAmt = SUM(Sales[Amount])
VAR Bonus = 
    IF(SalesAmt > 100000, 1000,
        IF(SalesAmt > 50000, 500, 0)
    )
RETURN Bonus

The name 'Bonus' has already been used in the expression. We can't declare VAR Bonus twice. Even though the intent is clear (initial value → then overwrite based on logic), DAX doesn’t allow it.

Correct Way (Use Separate Variables)

We should define a new variable with a different name, like this:

DAX

BonusCalc =
VAR SalesAmt = SUM(Sales[Amount])
VAR CalculatedBonus = 
    IF(SalesAmt > 100000, 1000,
        IF(SalesAmt > 50000, 500, 0)
    )
RETURN CalculatedBonus

Benefits of Using Variables

There are following benefits of using variables:

Readability
- Breaking down a complex formula into smaller, logical components makes it easier to understand.
Performance
- Expressions defined in a variable are calculated only once, reducing the overall computational cost.
Reusability
- A variable's value can be reused multiple times within the same DAX formula.
Debugging
- We can test individual components of a formula by returning the variable of interest.

Use Cases for Variables in DAX

Intermediate Calculations
- Storing partial results to avoid recalculating them multiple times.
Conditional Logic
- Using variables in IF or SWITCH statements to simplify decision-making logic.

DAX

ProfitMargin =
VAR TotalCost =
    SUM(Sales[Cost])
VAR TotalRevenue =
    SUM(Sales[Revenue])
RETURN
    IF(
        TotalCost > 0,
        (TotalRevenue - TotalCost) / TotalCost,
        BLANK()
    )

Key Points to Remember

Variables are scoped locally to the measure or calculated column in which they are defined.
Improving performance because variables are evaluated only once and only when or if they're needed, and they are immutable (cannot be modified after assignment).
We can define multiple variables, but each variable must have a unique name.

By using variables effectively, we can create optimized, maintainable, and efficient DAX formulas in Power BI.

Previous Next