DAX fundamentals — measures, CALCULATE, iterators and RELATED

Calculated columns vs measures

Rule of thumb: if you would put it on an axis or slicer, it is a column; if you would put it in Values, it is a measure.

Base measures

All examples use the Orion model: FactSales (order lines with order attributes), Products, Stores, Customers, Date.

Net Revenue :=
SUMX ( FactSales, FactSales[quantity] * FactSales[unit_price] * ( 1 - FactSales[discount_pct] ) )

Orders := DISTINCTCOUNT ( FactSales[order_id] )

Units := SUM ( FactSales[quantity] )

Avg Order Value := DIVIDE ( [Net Revenue], [Orders] )

• SUMX is an iterator: it walks the table row by row, evaluates the expression, then sums. Needed because revenue is a product of columns.
• DISTINCTCOUNT(order_id) counts orders, not lines — the fan-out lesson in DAX form.
• DIVIDE(a, b) handles division by zero (returns BLANK) — prefer it to /.
• Measures reference other measures: build a small library of base measures and reuse them.

What would a card visual show for each measure, filtered to completed orders in 2025? The same numbers you can compute in SQL:

SELECT ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 0) AS net_revenue,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.quantity) AS units,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed' AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';

CALCULATE: the most important function

CALCULATE(expression, filter1, filter2, …) evaluates an expression in a modified filter context. Filters inside CALCULATE replace existing filters on the same columns.

Completed Revenue :=
CALCULATE ( [Net Revenue], FactSales[status] = "completed" )

Online Revenue :=
CALCULATE ( [Completed Revenue], FactSales[channel] = "online" )

Online Share % := DIVIDE ( [Online Revenue], [Completed Revenue] )

In a table visual with one row per store, Online Revenue for the "Orion Online" row shows its revenue; for physical stores it is blank (they have no online orders). Online Share % on a card with no store filter = online's share of the total:

SELECT ROUND(100.0 * SUM(CASE WHEN o.channel = 'online' THEN oi.quantity * oi.unit_price * (1 - oi.discount_pct) END)
           / SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 1) AS online_share_pct_2025
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed' AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';

ALL: removing filters

ALL(table or column) returns all rows, ignoring filters. Inside CALCULATE it removes filters — essential for "share of total":

Revenue All Categories :=
CALCULATE ( [Completed Revenue], ALL ( Products[category] ) )

Category Share % := DIVIDE ( [Completed Revenue], [Revenue All Categories] )

In a table with one row per category, the denominator stays the grand total on every row, so the shares add to 100%. Related functions: ALLEXCEPT(table, columns_to_keep), REMOVEFILTERS() (clearer synonym for ALL as a CALCULATE modifier), ALLSELECTED() (respects slicer selections — "share of what the user selected").

FILTER: row-by-row conditions

Simple column conditions in CALCULATE (Products[category] = "Grocery") are fast. When the condition needs a measure or an expression across columns, use FILTER, which iterates a table and keeps rows meeting the condition:

Revenue from Large Orders :=
CALCULATE (
    [Completed Revenue],
    FILTER ( VALUES ( FactSales[order_id] ), [Net Revenue] >= 200 )
)

VALUES(FactSales[order_id]) gives the distinct orders in the current context; FILTER keeps orders whose revenue is at least 200; CALCULATE sums revenue for them.

COUNTROWS and DISTINCTCOUNT

Order Lines := COUNTROWS ( FactSales )
Customers   := DISTINCTCOUNT ( FactSales[customer_id] )
Lines per Order := DIVIDE ( [Order Lines], [Orders] )

COUNTROWS(table) counts rows in the current filter context — often clearer than COUNT(column).

RELATED and RELATEDTABLE

In a row context on the many side, RELATED fetches a value from the one side:

Line Margin (column in FactSales) =
FactSales[quantity] * ( FactSales[unit_price] * ( 1 - FactSales[discount_pct] ) - RELATED ( Products[unit_cost] ) )

Gross Margin := SUM ( FactSales[Line Margin] )
-- or, without a stored column:
Gross Margin (measure) :=
SUMX ( FactSales, FactSales[quantity] * ( FactSales[unit_price] * ( 1 - FactSales[discount_pct] ) - RELATED ( Products[unit_cost] ) ) )

RELATEDTABLE goes the other way (from a dimension row to its related fact rows), e.g. a calculated column in Customers counting their orders.

Practice

Summary

• Columns for slicing, measures for aggregating.
• Build base measures (SUMX, DISTINCTCOUNT, DIVIDE) and reuse them.
• CALCULATE modifies filter context; ALL/REMOVEFILTERS remove filters for totals and shares.
• Prefer column filters to FILTER over whole tables; use RELATED for row-level lookups.