DAX Variables: Writing Cleaner, Faster Measures

As measures grow, the same sub-expression tends to appear two or three times, nested functions pile up, and nobody can tell what the formula does. Variables fix all three. Microsoft's guidance lists their benefits plainly: they can improve performance, reliability and readability, and reduce complexity (Microsoft Learn: use variables). They also have one rule that catches almost everyone once, shown below with real numbers.

Syntax

A variable is declared with VAR, and the measure's result follows RETURN:

Sales YoY Growth % =
VAR SalesPriorYear =
    CALCULATE ( [Sales], PARALLELPERIOD ( 'Date'[Date], -12, MONTH ) )
RETURN
    DIVIDE ( [Sales] - SalesPriorYear, SalesPriorYear )

The reference sets the rules: a variable stores the result of an expression, and once calculated its value does not change, even when referenced in another expression (DAX reference: VAR). Names can use letters and digits, not starting with a digit, with an optional double-underscore prefix; spaces, quotes and brackets are not allowed. A variable can hold a single value or a whole table, and it can refer to measures and to variables declared before it.

Faster: evaluate once, use many times

Without the variable, the year-over-year measure above calculates "same period last year" twice, once in the numerator and once in the denominator. Microsoft reports that the variable version produces the same result in about half the query time, because the engine evaluates the stored expression only once. The saving grows with every repeated sub-expression.

Clearer: name the steps

A well-named variable turns a formula into a short story. Compare:

Margin Change pts =
VAR MarginThisYear = DIVIDE ( [Profit], [Sales] )
VAR MarginLastYear =
    CALCULATE ( DIVIDE ( [Profit], [Sales] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    MarginThisYear - MarginLastYear

Anyone reading MarginThisYear - MarginLastYear knows what the measure returns without unpicking nested functions.

Easier to debug: return one step

To check an intermediate result, temporarily return the variable instead of the final expression, and comment out the real return line, as Microsoft's guidance demonstrates:

RETURN
    -- DIVIDE ( [Sales] - SalesPriorYear, SalesPriorYear )
    SalesPriorYear

Put the measure in a table visual, check the intermediate values, then restore the original line.

The rule that surprises: variables are fixed where they are declared

Variables are evaluated in the filter context where they are declared, outside any filters the RETURN expression applies. Wrapping a variable in CALCULATE therefore changes nothing. A share-of-total measure written like this looks reasonable:

Share of All Countries (wrong) =
VAR CountryRevenue = [Revenue]
RETURN
    DIVIDE ( CountryRevenue, CALCULATE ( CountryRevenue, REMOVEFILTERS ( Sales[Country] ) ) )

The pandas code below reproduces what each version returns for four countries in a UK online retailer's sales (UCI: Online Retail):

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
sales = raw[~raw["InvoiceNo"].str.startswith("C") & ~raw["StockCode"].isin(services)
            & ~raw["StockCode"].str.startswith("gift_") & (raw["Quantity"] > 0) & (raw["UnitPrice"] > 0)]

def revenue(filter_context):
    rows = sales[filter_context]
    return (rows["Quantity"] * rows["UnitPrice"]).sum()

all_rows = pd.Series(True, index=sales.index)
cells = []
for country in ["Netherlands", "EIRE", "Germany", "France"]:
    cell = sales["Country"] == country
    this_revenue = revenue(cell)                        # VAR CountryRevenue = [Revenue], fixed in the cell's context
    cells.append({
        "Country": country,
        "Share, correct": f"{revenue(cell) / revenue(all_rows):.1%}",
        "Share, variable inside CALCULATE": f"{this_revenue / this_revenue:.1%}",
    })
print(pd.DataFrame(cells).to_string(index=False))

    Country Share, correct Share, variable inside CALCULATE
Netherlands           2.8%                           100.0%
       EIRE           2.6%                           100.0%
    Germany           2.0%                           100.0%
     France           1.8%                           100.0%

Every share comes out at 100%, because CountryRevenue was already calculated for the country before CALCULATE removed the filter. The fix is to put the measure, not the variable, inside CALCULATE:

Share of All Countries =
VAR CountryRevenue = [Revenue]
VAR AllRevenue = CALCULATE ( [Revenue], REMOVEFILTERS ( Sales[Country] ) )
RETURN
    DIVIDE ( CountryRevenue, AllRevenue )

DAX CALCULATE explained covers how the filter change itself works.

Simpler: variables replace EARLIER

The same rule is useful. Before variables existed, calculated columns that compared the current row with other rows needed the EARLIER function, which many modellers found hard to follow. Because a variable captures the current row's value before any iteration starts, it does the same job more readably. Microsoft's ranking example, adapted to a Country table with a Revenue column:

Revenue Rank =
VAR CurrentRevenue = Country[Revenue]
RETURN
    COUNTROWS ( FILTER ( Country, Country[Revenue] > CurrentRevenue ) ) + 1

countries = sales.assign(Revenue=sales["Quantity"] * sales["UnitPrice"]).groupby("Country")["Revenue"].sum().to_frame()
ranks = []
for country, row in countries.iterrows():
    current = row["Revenue"]                            # VAR CurrentRevenue = Country[Revenue]
    ranks.append((countries["Revenue"] > current).sum() + 1)
countries["Rank"] = ranks
print(countries.sort_values("Rank").head(5).round(0).astype({"Revenue": "int64"}).to_string())

                Revenue  Rank
Country                      
United Kingdom  8747562     1
Netherlands      283889     2
EIRE             271164     3
Germany          205570     4
France           184583     5

Each row counts how many countries have more revenue than its own stored value, then adds one. For measures that need a ranking responsive to filters, the RANKX function is usually the better tool; FILTER and ALL explains the filter logic it relies on.

Guard clauses: return blank when an input is missing

Variables make it cheap to check inputs before calculating. The VAR reference's own example returns a growth rate only when both this year's and last year's sales exist:

YoY% =
VAR Sales = SUM ( Sales[Sales Amount] )
VAR SalesLastYear =
    CALCULATE ( SUM ( Sales[Sales Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF ( Sales && SalesLastYear, DIVIDE ( Sales - SalesLastYear, SalesLastYear ) )

In the first year of data there is no prior year, so the measure returns blank instead of a misleading figure. Without variables, the same check would repeat both calculations inside the IF and again inside DIVIDE.

Where a variable is declared matters

A variable declared at the top of a measure is evaluated once, in the cell's filter context. A variable declared inside an iterator's expression, such as the second argument of SUMX, is evaluated again for every row the iterator visits, in that row's context. Both are useful, but they are different: decide whether you need one value for the whole cell or one value per row, and declare the variable at that level.

When not to use a variable

If a value must be recalculated under different filters within the same measure, such as this year and last year, or one country and all countries, it has to be a measure reference evaluated inside each CALCULATE, not a variable. Variables store answers; measures store questions that can be asked again in a new context. Keeping that distinction in mind prevents the 100% share bug above.

Habits for using variables well

• Declare a variable for anything used twice.
• Name variables for their meaning, such as SalesPriorYear, not x or temp.
• Keep CALCULATE around measures, never around variables that should respond to the new filter.
• Use table variables for filtered sets you reuse, remembering that columns inside a table variable cannot be referenced with the usual Table[Column] syntax.
• Leave a debugging return commented out while developing complex measures, and remove it when done.

For the basics of measures and context, see the DAX guide. Advanced DAX formulas are part of the DAX module of the Power BI course.

Sources

• Use variables to improve your DAX formulas (Microsoft Learn) — Microsoft Learn
• VAR keyword (DAX reference, Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository