Row context, filter context, context transition and time intelligence

Filter context

The filter context is the set of filters active when a measure is evaluated: slicers, page/visual filters, and the row/column headers of the visual cell. In a matrix with stores on rows and months on columns, the cell Baku Central × 2025-03 evaluates [Completed Revenue] with filters Stores[store_name] = "Orion Baku Central" and Date[YearMonth] = "2025-03" (plus any slicers).

The total row has a different filter context — no store filter — which is why totals are recomputed, not summed from the rows. For additive measures this gives the same number; for ratios (margin %, distinct counts) it correctly does not.

-- Why a 'Customers' total is not the sum of the rows: distinct customers per channel vs overall (2025, completed)
SELECT channel, COUNT(DISTINCT customer_id) AS customers
FROM orders WHERE status = 'completed' AND order_date >= '2025-01-01'
GROUP BY channel
UNION ALL
SELECT 'TOTAL', COUNT(DISTINCT customer_id)
FROM orders WHERE status = 'completed' AND order_date >= '2025-01-01';

The total is smaller than store + online because many customers bought in both channels. A DAX DISTINCTCOUNT total behaves exactly the same way — correctly.

Row context

A row context exists when DAX iterates rows: in a calculated column, or inside SUMX, FILTER, ADDCOLUMNS. It knows "the current row" but does not filter anything by itself.

In a calculated column in Products:

Total Units (wrong) = SUM ( FactSales[quantity] )

returns the grand total on every product row — the row context does not filter FactSales.

Context transition

CALCULATE converts the current row context into an equivalent filter context — context transition. Every measure reference is implicitly wrapped in CALCULATE, so:

Total Units (correct) = CALCULATE ( SUM ( FactSales[quantity] ) )
-- or simply, if [Units] is a measure:
Total Units (correct) = [Units]

Now each product row filters FactSales to that product. This is powerful — and a source of surprises:

Avg Daily Revenue :=
AVERAGEX ( VALUES ( 'Date'[Date] ), [Completed Revenue] )

For each date in context, [Completed Revenue] is evaluated for that date (context transition), then averaged. Days with no sales return BLANK and are skipped by AVERAGEX — decide whether that is the business definition you want ("average per trading day") or whether you need zeros ("average per calendar day").

Time intelligence

With a marked date table related to the fact:

Revenue YTD :=
TOTALYTD ( [Completed Revenue], 'Date'[Date] )

Revenue PY :=
CALCULATE ( [Completed Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Revenue YoY % :=
DIVIDE ( [Completed Revenue] - [Revenue PY], [Revenue PY] )

Revenue Rolling 3M :=
CALCULATE (
    [Completed Revenue],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

What a monthly matrix would show for 2025 — revenue, prior year and YoY % — is reproduced here in SQL:

WITH m AS (
  SELECT strftime('%Y', o.order_date) AS y, strftime('%m', o.order_date) AS mo,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS rev
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' GROUP BY 1, 2
)
SELECT cur.mo AS month, ROUND(cur.rev) AS revenue_2025, ROUND(py.rev) AS revenue_py,
       ROUND(100 * (cur.rev / py.rev - 1), 1) AS yoy_pct,
       ROUND(SUM(cur.rev) OVER (ORDER BY cur.mo)) AS ytd_2025
FROM m cur JOIN m py ON py.mo = cur.mo AND py.y = '2024'
WHERE cur.y = '2025'
ORDER BY cur.mo;

Advanced DAX patterns

• Variables (VAR … RETURN) store intermediate results, improve readability and avoid repeated evaluation:

Revenue YoY % :=
VAR Cur = [Completed Revenue]
VAR PY  = CALCULATE ( [Completed Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN DIVIDE ( Cur - PY, PY )

• Ranking: RANKX(ALL(Products[product_name]), [Completed Revenue]).
• Top N with others: TOPN + a disconnected table, or the visual-level Top N filter.
• Dynamic segmentation: a disconnected band table and a measure that counts customers whose revenue falls in each band.
• Calculation groups (Tabular Editor): one definition of YTD/PY/YoY applied to many measures.

Debugging checklist

1. Put the measure in a table visual with the problem columns to see per-row values.
2. Check relationships and filter direction for the columns involved.
3. Test the inner expression separately (a temporary measure).
4. Use DAX query view / Performance Analyzer to inspect queries.
5. Reconcile with SQL.

Practice

Summary

• Filter context = active filters for a cell; totals are recomputed in their own context.
• Row context iterates but does not filter; CALCULATE (and any measure reference) performs context transition.
• Time intelligence needs a marked date table: TOTALYTD, SAMEPERIODLASTYEAR, DATESINPERIOD; handle partial periods.
• Use variables, reconcile with SQL, and debug in table visuals.