Downloads
DAXFilter and contextBeginner

VAR

Stores a value or a table in a named variable that the RETURN part can reuse.

Syntax
VAR <name> = <expression> [VAR <name> = <expression> ...] RETURN <expression>

Returns: Whatever the RETURN expression returns.

What it does

VAR names an intermediate result, so a formula reads like a set of steps and each calculation runs once. A typical use takes total sales and total cost, then computes margin and margin percent from the named pieces. The rule to learn is that a variable is evaluated where it is written. Its value does not change when a later CALCULATE changes the filter.

Syntax and arguments

Example

Gross margin percent, with the pieces named so an auditor can follow the logic.

Gross Margin % =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
VAR Margin = Revenue - Cost
RETURN
    DIVIDE ( Margin, Revenue )

Each measure is evaluated once, in the current context. The formula reads top to bottom. DIVIDE returns blank when revenue is zero, instead of an error.

Example: a second use

Count the stores above a threshold, with the threshold and the store list each set once.

Stores Over Threshold =
VAR Threshold = 1000000
VAR StoreList = VALUES ( Stores[StoreName] )
RETURN
    COUNTROWS (
        FILTER (
            StoreList,
            [Total Sales] > Threshold
        )
    )

The variable StoreList holds a table, and FILTER loops over it. The threshold is written once, so changing it means editing one line.

Things to watch

In business terms

Almost every governed measure uses variables: margin, budget variance, variance percent, aging buckets and allocation shares. Named steps let a reviewer check each piece of the logic in a formula review.

Related functions

All DAX functions · View as Markdown · DAX fundamentals