skip to content

In DAX, why doesn't CALCULATE change the value of a VAR defined before it?

level: seniorimportance: nice to knowfreq 32%

answer

  1. it stores a result, not a formula
  2. when exactly is the expression evaluated
  3. the name later refers to a constant
  4. this is also why EARLIER is rarely needed

basics

~20 s

A DAX variable is evaluated once, in the context where it is defined, and its result is then a constant. CALCULATE later in the expression modifies the filter context for what follows, but cannot re-evaluate a value that has already been computed.

solid answer

~50 s

`VAR` binds a value, not a formula. DAX evaluates the variable's expression in the filter and row context that exists at the point of definition, and from then on the name refers to that computed result. So this measure does not do what it looks like it does: ``` VAR CurrentSales = SUM ( Sales[Amount] ) RETURN CALCULATE ( CurrentSales, SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) ``` `CurrentSales` was already resolved under the current period, so CALCULATE shifts the context around a constant and you get this year's number back. The fix is to keep the expression inside CALCULATE — `CALCULATE ( SUM ( Sales[Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )` — or to define the variable *after* the context change. The same property is what makes variables valuable: evaluate once, reuse many times, no accidental re-evaluation under a shifted context, and each variable is a natural debugging checkpoint you can RETURN on its own.

code

dax · 10 lines
dax
-- broken: the variable is already a constant
Sales LY Broken =
VAR CurrentSales = SUM ( Sales[Amount] )
RETURN CALCULATE ( CurrentSales, SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

-- fixed: aggregate inside the modified context
Sales LY =
VAR LastYearSales =
    CALCULATE ( SUM ( Sales[Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN LastYearSales

go deeper

for a junior

Recall that VAR ... RETURN stores an intermediate value and improves readability, and that the value is computed where it is defined.

for a middle

Explain that a variable holds a computed result, so later context changes cannot alter it, and identify the prior-year measure that silently returns the current period because of this.

for a senior

Use the capture semantics deliberately — saving an outer value before ALL or before an inner iteration — and demonstrate debugging a long measure by returning one variable at a time.

for a principal

Push it as a code standard: measures built from named variables are reviewable and testable by people who did not write them, which matters more than cleverness once a measure library is shared across teams.

## Variables bind values, not expressions DAX's `VAR name = expression RETURN body` looks like a local definition, and people read it the way they read a macro or an alias — as text that gets substituted wherever the name appears. It is not. The expression is evaluated, once, producing a value, and the name is bound to that value. The context in force at the moment of evaluation is the context at the point of *definition*, not the point of use. Once you internalise that one sentence, the surprising behaviour stops being surprising and starts being useful. ## The canonical bug ``` Sales LY Broken = VAR CurrentSales = SUM ( Sales[Amount] ) RETURN CALCULATE ( CurrentSales, SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) ``` The author's intent is clear: name the aggregate, then evaluate it over last year. What happens is that `CurrentSales` is computed under the visual's current filter context — say March 2024 — yielding a scalar. CALCULATE then builds a filter context for March 2023 and evaluates the expression `CurrentSales`, which is a constant. The constant does not care about filters. Every cell shows the current period's number, and because the shape looks plausible the error can survive review. The correct forms: ``` -- keep the aggregation inside the modified context Sales LY = CALCULATE ( SUM ( Sales[Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) -- or define the variable after the shift Sales LY Var = VAR LastYearSales = CALCULATE ( SUM ( Sales[Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) RETURN LastYearSales ``` ## Row context is captured the same way Inside an iterator, a variable defined before the iterator does not see the iterator's rows — it was evaluated outside. That is often exactly what you want, and it is the standard trick for capturing an outer row's value so you can compare it against inner rows without needing `EARLIER`. Variables largely retired `EARLIER` for this reason: instead of asking DAX to reach out to an enclosing row context, you save the value in a variable before entering the inner iteration and reference it by name. ``` Running Total = VAR CurrentDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( [Total Sales], 'Date'[Date] <= CurrentDate, ALL ( 'Date' ) ) ``` Here the capture is the whole point: `CurrentDate` is fixed before `ALL ( 'Date' )` wipes the date filter, so the comparison still knows where "now" is. Written without the variable, `MAX ( 'Date'[Date] )` inside the CALCULATE would see the unfiltered calendar and return the end of the model. ## Evaluate once, use many times The performance argument is the other half. A variable's expression is evaluated at most once per evaluation of the containing expression, no matter how many times the name appears, and it is only evaluated if it is actually needed. Repeating an expensive sub-expression three times in a nested IF can make the engine compute it three times; hoisting it into a variable does not. This is why the recommended style for anything non-trivial is a chain of variables ending in a short RETURN. ## Variables as a debugging surface Because each variable is a complete value, you can temporarily change the RETURN to expose any one of them and see exactly what that step produced. On a long measure this is far more effective than reasoning about the whole expression, and it is the answer worth giving when an interviewer asks how you debug DAX. Variables can hold tables as well as scalars, so an intermediate filtered table can be inspected with `COUNTROWS` or returned into a table visual during development. ## Naming and readability Since the variable is a value, name it for what the value *is* — `LastYearSales`, `SelectedCategory`, `TopCustomers` — rather than for what the formula does. A measure written as five well-named variables and a two-line RETURN is reviewable by someone who does not know the model; the same logic inlined into nested CALCULATEs is not. In interviews this is usually the differentiator: the candidate who uses variables for clarity and the one who uses them to avoid re-evaluation are both fine, but the one who knows *why* CALCULATE cannot touch them has actually understood the evaluation model.

  • How do variables replace EARLIER in nested iterators?
    EARLIER exists to reach the enclosing row context from inside an inner iteration. With variables you capture the outer row's value before entering the inner iterator and refer to it by name, which is both clearer and immune to the nesting-depth confusion EARLIER creates. The captured-value semantics are exactly what makes the substitution safe.
  • Does defining a variable that is never referenced cost anything?
    No. DAX evaluates a variable only when its value is actually needed, so an unused variable in a branch that is not taken is not computed. That makes it safe to define several variables up front for readability, though the reverse also holds: you cannot rely on a variable's evaluation as a side effect, because DAX has none.
  • How would you debug a long DAX measure built from variables?
    Temporarily change the RETURN to expose one variable at a time and inspect the intermediate value in a card or table visual. Because each variable is a complete scalar or table, you can verify the chain step by step rather than reasoning about the whole expression. Table-valued variables can be checked with COUNTROWS or returned directly during development.

A variable is a photograph, not a live camera feed: it captures the scene at the moment you press the shutter, and rearranging the room afterwards does not change the picture.

saying these in an interview costs you the question

  • Treating VAR as textual substitution like a macro
  • Expecting CALCULATE to re-evaluate a variable under new filters
  • Thinking a variable is re-evaluated per iterated row when defined outside
  • Claiming variables are only a readability nicety with no evaluation semantics
  • Reaching for EARLIER when a captured variable would be clearer

context

resolving links…