PathTailor
FRSign inBuild my path
PathTailor
Build my pathSign inLire en français

Row Context, Filter Context and Context Transition

First lesson of the Advanced DAX (Context, Time Intelligence) course, free to read: about 20 minutes, 3 exercises and 3 quiz questions. This is the catalogue's base lesson; in the student space, it is rewritten for your profile.

Objectives

  • Precisely distinguish row context from filter context
  • Explain and use context transition
  • Predict the result of a formula in a calculated column or an iterator

Row contextFilter contextContext transition

Two Contexts, Two Roles

DAX evaluates each expression in a context. There are two kinds, and most surprises come from confusing them.

Row context Filter context
Where? calculated columns, iterators (SUMX, FILTER…) visual cells, slicers, CALCULATE
Effect “I’m on this row”: you can read its columns “only these rows are visible”
Propagates through relationships? no (you need RELATED) yes, from the one side to the many side

The Experiment That Explains Everything

Add to DimMagasin (2 rows) two calculated columns:

CA sans CALCULATE = SUM ( FaitVentes[Montant] )
CA avec CALCULATE = CALCULATE ( SUM ( FaitVentes[Montant] ) )
  • The first displays 3,925 on both rows. The row context knows we are on Lyon, but it does not filter FaitVentes: SUM sees the whole table.
  • The second displays 2,185 for Lyon and 1,740 for Lille. CALCULATE took the current row (CodeMagasin = M1, Magasin = Lyon) and turned it into a filter, which then propagates to FaitVentes through the relationship.

This is context transition: CALCULATE (and CALCULATETABLE) converts the entire active row context into a filter context (a filter on each column of the row).

The Measure Hides a CALCULATE

Write a third column:

CA via mesure = [Total ventes]

Result: 2,185 and 1,740. Every reference to a measure is implicitly wrapped in a CALCULATE: [Total ventes] is equivalent to CALCULATE ( SUM ( FaitVentes[Montant] ) ). That is why your iterators over dimensions worked in the previous course:

Meilleur mois = MAXX ( VALUES ( Dates[Année-Mois] ), [Total ventes] )

For each month, the measure is evaluated with the month as a filter; MAXX keeps the maximum: 290 (December 2025, all stores). For Lyon alone: 160; for Lille: 130. If you replace [Total ventes] with SUM ( FaitVentes[Montant] ), there is no more transition: each month returns the context’s total, and the result is wrong (3,925 with no filter).

Counting Items According to a Condition

The same mechanism lets you count months:

Nb mois > 200 =
COUNTROWS ( FILTER ( VALUES ( Dates[Année-Mois] ), [Total ventes] > 200 ) )

Result: 13 months (7 in 2025, 6 in 2026).

The Traps of Transition

  1. Cost: in SUMX ( FaitVentes, [Total ventes] ), the transition is triggered for each fact row, which can be very slow on millions of rows. Iterate over a dimension or a low-cardinality column.
  2. Duplicate rows: the transition filters on all columns of the row. If two fact rows are identical, each one “sees” both, and the result is doubled. Iterators over a fact table without a unique key using a measure are therefore dangerous.
  3. Readability: when reading a formula, spot every measure referenced in an iterator: it signals a transition.

A Method for Predicting a Result

For any formula, ask yourself three questions:

  1. What is the starting filter context (cell, slicers)?
  2. Is there a row context (calculated column, iterator)?
  3. Is there a CALCULATE, explicit or hidden in a measure, that turns the row into a filter?

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

  1. TaskDifficulty 21 hint

    Highlight context transition

    Course dataset. Create C:\Formation\ventes_mensuelles.csv (UTF-8) with the 36 rows below (one aggregated sale per store and per month, from January 2025 to June 2026):

    Date,CodeMagasin,Montant
2025-01-15,M1,100
2025-01-15,M2,80
2025-02-15,M1,110
2025-02-15,M2,85
2025-03-15,M1,120
2025-03-15,M2,90
2025-04-15,M1,105
2025-04-15,M2,95
2025-05-15,M1,115
2025-05-15,M2,100
2025-06-15,M1,130
2025-06-15,M2,105
2025-07-15,M1,90
2025-07-15,M2,70
2025-08-15,M1,80
2025-08-15,M2,60
2025-09-15,M1,125
2025-09-15,M2,95
2025-10-15,M1,135
2025-10-15,M2,100
2025-11-15,M1,140
2025-11-15,M2,110
2025-12-15,M1,160
2025-12-15,M2,130
2026-01-15,M1,120
2026-01-15,M2,90
2026-02-15,M1,115
2026-02-15,M2,100
2026-03-15,M1,130
2026-03-15,M2,95
2026-04-15,M1,125
2026-04-15,M2,105
2026-05-15,M1,135
2026-05-15,M2,110
2026-06-15,M1,150
2026-06-15,M2,120

    Then: import it into the FaitVentes table (Date as Date type, Montant as whole number); create DimMagasin with Home > Enter data (CodeMagasin, Magasin: M1, Lyon and M2, Lille); create the Dates table in DAX:

    Dates =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Année", YEAR ( [Date] ),
    "Numéro mois", MONTH ( [Date] ),
    "Année-Mois", FORMAT ( [Date], "yyyy-mm" )
)

    Mark Dates as a date table, link Dates[Date] → FaitVentes[Date] and DimMagasin[CodeMagasin] → FaitVentes[CodeMagasin], and create Total ventes = SUM ( FaitVentes[Montant] ). Check: Total ventes = 3,925.

    1. In DimMagasin, create the calculated columns CA sans CALCULATE, CA avec CALCULATE and CA via mesure.
    2. Create the measures Meilleur mois (MAXX with the measure) and Meilleur mois (faux) (MAXX with SUM ( FaitVentes[Montant] )).
    3. Place both measures in a table by store, with a total row.
    • CA sans CALCULATE = 3,925 on both rows
    • CA avec CALCULATE and CA via mesure = 2,185 (Lyon) and 1,740 (Lille)
    • Meilleur mois: Lyon 160, Lille 130, total 290
    • Meilleur mois (faux): Lyon 2,185, Lille 1,740, total 3,925

  2. CodeDifficulty 21 hint

    Count months above a threshold

    Write the measure Nb mois > 200 that counts the months (column Dates[Année-Mois]) whose [Total ventes] is strictly greater than 200.

    Nb mois > 200 =
    -- à compléter

  3. Multiple choiceDifficulty 31 hint

    Predict the result

    In a calculated column of DimMagasin, what does COUNTROWS ( FaitVentes ) return?

    • The number of sales of the row's store
    • The total number of rows in FaitVentes (36) on every row
    • An error
    • BLANK

End-of-lesson quiz

  1. What is context transition?

    • The conversion of row context into filter context by CALCULATE
    • Moving from one page to another
    • Sorting a column

  2. Why does [Total ventes] in a calculated column of DimMagasin give the store’s revenue?

    • Because a measure is implicitly wrapped in CALCULATE
    • Because calculated columns always filter
    • Because the relationship is bidirectional

  3. What is the risk of SUMX ( FaitVentes, [Total ventes] ) on a large table?

    • None
    • One context transition per row: slowness and possible duplicates
    • A syntax error

The rest of the course

  1. CALCULATE Modifiers: ALL, ALLEXCEPT, ALLSELECTED, KEEPFILTERS20 min
  2. Time Intelligence: Year-to-Date Totals and Prior-Year Comparisons20 min
  3. Running Totals, Moving Averages, Rankings and Window Functions20 min
  4. Calculation Groups, Performance and Debugging20 min
Build my pathSee the full syllabus

Advanced DAX (Context, Time Intelligence) · Advanced · 1 h 40 min