DAX Essentials: Variables & Functions | Guided by A Guide to Cloud

DAX for DP-600

Simple explanation

Think of DAX like a spreadsheet formula language that works on entire tables.

In Excel, you write formulas for individual cells. In DAX, you write formulas that operate on columns and tables. A SUM in Excel adds a range of cells. A SUM in DAX adds an entire column, filtered by whatever context the report visual provides.

This module covers the DAX patterns the exam tests most: variables (store intermediate results), iterators (row-by-row calculations), table filtering (CALCULATE), windowing (ranking, running totals), and information functions (type checking).

Variables: VAR / RETURN

Variables store intermediate results, improving readability and performance (the expression is evaluated once, not twice).

Profit Margin =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR Margin =
    DIVIDE(TotalRevenue - TotalCost, TotalRevenue)
RETURN
    Margin

What’s happening:

VAR TotalRevenue — calculates total revenue once and stores it
VAR TotalCost — calculates total cost once
VAR Margin — computes margin using both stored values
RETURN — returns the final result

Without variables, you would write DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue])) — SUM(Sales[Revenue]) is evaluated twice.

Iterators: SUMX, AVERAGEX, MAXX, COUNTX

Iterators evaluate an expression row by row across a table, then aggregate the results.

SUMX — row-by-row sum

Total Revenue (Extended) =
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

What’s happening: For each row in the Sales table, multiply Quantity by UnitPrice. Then sum all the results. This handles the case where revenue is not pre-calculated in a column.

AVERAGEX — row-by-row average

Average Transaction Value =
AVERAGEX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

When to use iterators vs simple aggregators

Use	When
`SUM(column)`	The value is already in a column
`SUMX(table, expression)`	The value needs calculation per row
`AVERAGE(column)`	Average of a single column
`AVERAGEX(table, expression)`	Average of a per-row calculation

Exam tip: Iterator performance

Iterators evaluate every row in the table. For a fact table with 200 million rows, SUMX processes 200 million calculations. This is usually fast (the VertiPaq engine is optimised for this), but:

Nested iterators (SUMX inside SUMX) can be extremely slow — avoid if possible
CALCULATE inside an iterator changes the filter context per row — powerful but expensive
If the calculation can be pre-computed as a column, a simple SUM is faster than SUMX

Table filtering: CALCULATE

CALCULATE is the most important DAX function. It evaluates an expression in a modified filter context.

Previous Year Revenue =
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(Dates[Date])
)

What’s happening:

Start with the current filter context (whatever the visual is showing)
Modify the date filter to show the same period last year
Evaluate Total Revenue in this modified context

Common CALCULATE modifiers

Modifier	What It Does	Example
`ALL(table)`	Remove all filters from a table	% of Grand Total
`ALLEXCEPT(table, column)`	Remove all filters except the specified column	% of Category Total
`FILTER(table, condition)`	Apply a row-by-row filter	Sales where amount > 1000
`KEEPFILTERS(filter)`	Add filter without overriding existing ones	Intersect, not replace
Time intelligence functions	Shift or expand date ranges	YTD, QTD, previous year

Percentage of total pattern

% of Total Revenue =
VAR CurrentRevenue = [Total Revenue]
VAR AllRevenue =
    CALCULATE(
        [Total Revenue],
        ALL(Stores)
    )
RETURN
    DIVIDE(CurrentRevenue, AllRevenue)

What’s happening:

CurrentRevenue — revenue in the current filter context (e.g., one region)
AllRevenue — revenue with ALL store filters removed (grand total)
DIVIDE — safe division that returns BLANK instead of error for division by zero

Windowing functions: OFFSET, WINDOW, INDEX

Windowing functions (added in recent DAX updates) provide relative row access — similar to SQL window functions.

OFFSET — access relative rows

Previous Month Revenue =
CALCULATE(
    [Total Revenue],
    OFFSET(-1, ALLSELECTED(Dates[Month]), ORDERBY(Dates[Month]))
)

WINDOW — access a range of rows

3-Month Moving Average =
AVERAGEX(
    WINDOW(
        -2, REL, 0, REL,
        ALLSELECTED(Dates[Month]),
        ORDERBY(Dates[Month])
    ),
    [Total Revenue]
)

Information functions

Information functions check data types, blank values, and context:

Function	Returns	Use Case
`ISBLANK(value)`	TRUE if blank	Conditional formatting, error handling
`HASONEVALUE(column)`	TRUE if exactly one value in context	Dynamic titles (“Revenue for [Region]“)
`SELECTEDVALUE(column, default)`	The single selected value, or default	Slicer-driven parameters
`ISINSCOPE(column)`	TRUE if the column is in the current grouping	Subtotal vs detail row logic
`ISFILTERED(column)`	TRUE if a filter is active on the column	Conditional measure behavior

Dynamic Title =
IF(
    HASONEVALUE(Stores[Region]),
    "Revenue for " & SELECTEDVALUE(Stores[Region]),
    "Revenue — All Regions"
)

Question

Why use VAR/RETURN in DAX?

Click or press Enter to reveal answer

Answer

Variables (VAR) store intermediate results for reuse. Benefits: (1) Readability — complex measures broken into named steps. (2) Performance — the expression is evaluated once, not multiple times. (3) Debugging — you can inspect intermediate values.

Click to flip back

Question

What is the difference between SUM and SUMX?

Click or press Enter to reveal answer

Answer

SUM(column) aggregates an existing column. SUMX(table, expression) evaluates an expression row-by-row then sums the results. Use SUMX when the value needs per-row calculation (e.g., Quantity * Price). Use SUM when the value already exists as a column.

Click to flip back

Question

What does CALCULATE do?

Click or press Enter to reveal answer

Answer

CALCULATE evaluates an expression in a modified filter context. It takes a measure expression and one or more filter arguments. Example: CALCULATE([Revenue], Dates[Year] = 2026) evaluates Revenue with the date filter set to 2026, regardless of what the visual is showing.

Click to flip back

Question

What does HASONEVALUE do?

Click or press Enter to reveal answer

Answer

HASONEVALUE(column) returns TRUE if exactly one distinct value exists in the current filter context for that column. Used for dynamic titles, conditional formatting, and slicer-driven parameters. Example: IF(HASONEVALUE(Region), SELECTEDVALUE(Region), 'All Regions').

Click to flip back

Knowledge Check

Raj at Atlas Capital writes a measure: Profit Margin = DIVIDE(SUM(Trades[Revenue]) - SUM(Trades[Cost]), SUM(Trades[Revenue])). His colleague says this can be improved. How?

Knowledge Check

Anita at FreshCart needs a measure that calculates the average revenue per transaction, where revenue = Quantity x UnitPrice (there is no pre-calculated revenue column). Which DAX function should she use?

🎬 Video coming soon

Next up: Calculation Groups & Field Parameters — reduce measure proliferation with reusable calculation patterns.