Power BI Lesson 35 – Row & Filter Context | Dataplexa

DAX · Lesson 35

Row Context and Filter Context

Context is the single concept that separates DAX beginners from DAX practitioners. Two types of context govern every DAX calculation — filter context and row context. They look similar on the surface, they interact in non-obvious ways, and confusing them produces measures that appear to work correctly on a grand total but silently produce wrong values for every individual row. This lesson maps out both context types completely, shows exactly how they interact, and explains the context transition that CALCULATE triggers.

The Working Dataset

Orders and Customers tables — used throughout this lesson

Orders

OrderID	Revenue	Region	CustID
1001	1,200	North	C101
1002	850	South	C102
1003	430	North	C101
1004	95	South	C103
1005	1,540	West	C102

Customers

CustID	Name	Tier
C101	Alice Brown	Premium
C102	Bob Singh	Standard
C103	Carol Lee	Standard

Filter Context — What a Measure Sees

Filter context is the set of filters active at the moment a measure is evaluated. It is built from four sources: report-level filters, page-level filters, visual-level filters, and cross-filter effects from other visuals. A measure cannot see or count or sum any row that has been filtered out by the current filter context. This is not a restriction — it is the entire mechanism that makes slicers work.

Table visual — SUM(Orders[Revenue]) evaluated in different filter contexts

No slicer — full context

Region	Revenue
North	1,630
South	945
West	1,540
Total	4,115

Each Region row: filter context = that region only

Slicer = North

Region	Revenue
North	1,630
Total	1,630

Slicer adds Region="North" to every cell's filter context

Slicer = North + South

Region	Revenue
North	1,630
South	945
Total	2,575

West rows filtered out entirely by the slicer

Row Context — What a Calculated Column Sees

Row context is active when DAX evaluates a calculated column or an iterator function. It means "for this specific row, these are the values." Row context does not filter other rows — it just gives the current expression access to column values of the current row. It does not propagate through relationships on its own; RELATED() is required to cross a relationship inside row context.

// Row context in a calculated column — each row sees its own values
// New column added to Orders table:

Margin = Orders[Revenue] - Orders[Cost]
// Row context: for row 1001, [Revenue]=1200, [Cost]=800 → 400
// For row 1002, [Revenue]=850,  [Cost]=600 → 250

Margin % = DIVIDE(Orders[Revenue] - Orders[Cost], Orders[Revenue])
// Row 1001: (1200-800)/1200 = 33.3%
// Row 1002: (850-600)/850   = 29.4%

-- Cross-relationship with RELATED() — brings customer tier into row context
CustomerTier = RELATED(Customers[Tier])
// Row 1001: CustID=C101 → Customers[Tier] for C101 = "Premium"
// Row 1002: CustID=C102 → Customers[Tier] for C102 = "Standard"

Calculated columns — row context in action

OrderID	Revenue	Cost	Margin	Margin %	CustomerTier
1001	1,200	800	400	33.3%	Premium
1002	850	600	250	29.4%	Standard
1003	430	290	140	32.6%	Premium
1004	95	60	35	36.8%	Standard
1005	1,540	980	560	36.4%	Standard

Each row computes independently using only its own column values · RELATED() traverses the relationship to bring Customers[Tier] into each row's context

The Critical Difference — Row Context Does Not Filter

This is the source of the most common DAX bug. When you reference a column inside a calculated column without an aggregation function, you get the current row's value. But when you try to use SUM() inside a calculated column without CALCULATE(), you still get the grand total — not the sum for "this row's context." Row context does not become filter context automatically. Only CALCULATE() performs that transition.

// Bug: trying to get "revenue for this row's region" in a calculated column
-- Column attempt (WRONG):
Revenue for My Region = SUM(Orders[Revenue])
// This returns 4115 for EVERY row — the grand total
// Row context does not restrict what SUM sees
// SUM ignores row context entirely

Wrong column — SUM ignores row context

OrderID	Region	Revenue	Revenue for My Region (wrong)
1001	North	1,200	4,115 ❌
1002	South	850	4,115 ❌
1003	North	430	4,115 ❌
1004	South	95	4,115 ❌
1005	West	1,540	4,115 ❌

SUM sees all 5 rows regardless of which row's context is active — grand total returned everywhere

CALCULATE — The Context Transition Function

CALCULATE is the most important function in DAX. It does two things simultaneously: it evaluates an expression, and it modifies the filter context that expression runs in. When called inside a row context (a calculated column or iterator), CALCULATE also performs a context transition — it converts the current row context into an equivalent filter context, so aggregations become aware of "this row's values."

// CALCULATE syntax:
// CALCULATE(expression, filter1, filter2, ...)

// Fix: use CALCULATE to convert row context into filter context
-- Calculated column (CORRECT):
Revenue for My Region =
    CALCULATE(
        SUM(Orders[Revenue]),
        Orders[Region] = Orders[Region]  -- explicit filter won't work
    )
-- Actually the cleanest approach:
Revenue for My Region =
    CALCULATE(
        SUM(Orders[Revenue])
        -- No additional filter needed: CALCULATE performs context transition
        -- The row context (current Region value) becomes the filter
    )
// Row 1001 (North): CALCULATE converts row context → filter context
//   Filter context becomes: Region = "North"
//   SUM sees only North rows → 1,200 + 430 = 1,630
// Row 1002 (South): filter context becomes Region = "South"
//   SUM sees only South rows → 850 + 95 = 945

CALCULATE context transition — Revenue for My Region (correct)

OrderID	Region	Revenue	Revenue for My Region ✓
1001	North	1,200	1,630
1002	South	850	945
1003	North	430	1,630
1004	South	95	945
1005	West	1,540	1,540

CALCULATE converts each row's Region value into a filter · Orders 1001 and 1003 both return 1,630 (total North revenue) · West has only one order so 1,540 = its own revenue

CALCULATE — Modifying Filter Context in Measures

In measures, CALCULATE's primary role is adding, replacing, or removing filter context conditions. Each filter argument either adds a new filter, replaces an existing one on the same column, or (with ALL/REMOVEFILTERS) removes filters entirely.

// Pattern 1 — Add a filter (intersects with existing context)
North Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        Orders[Region] = "North"
    )
// In a visual already filtered to Premium customers:
//   Filter context = Premium AND North
//   Returns revenue for Premium customers in North only

North Revenue — always shows North regardless of other context

Region (axis)	Total Revenue	North Revenue
North	1,630	1,630
South	945	1,630
West	1,540	1,630
Total	4,115	1,630

CALCULATE replaces the Region filter with "North" for every cell — useful for comparison bars showing "North benchmark" alongside each region's own revenue

// Pattern 2 — Remove all filters (grand total regardless of context)
Grand Total Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        ALL(Orders)
    )

// Pattern 3 — Remove filter from one column only
All Regions Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        ALL(Orders[Region])   -- removes Region filter, keeps others
    )

// Pattern 4 — ALLSELECTED (respects slicer, ignores visual row filter)
// Used for % of total that resets with the slicer
Revenue % of Total =
    DIVIDE(
        SUM(Orders[Revenue]),
        CALCULATE(SUM(Orders[Revenue]), ALLSELECTED(Orders))
    )

ALL vs ALLSELECTED — behaviour when slicer = North + South

Region	Total Revenue	CALCULATE ALL(Orders)	ALLSELECTED(Orders)
North	1,630	4,115	2,575
South	945	4,115	2,575
Total	2,575	4,115	2,575

ALL ignores the slicer completely — always returns grand total across all data · ALLSELECTED respects the slicer — denominator = what the user filtered to (2,575 when North+South selected)

EARLIER() — Row Context Inside Row Context

EARLIER() accesses the value from an outer row context when you are inside a nested row context. It is mainly used in calculated columns to compare a row's value against aggregations of rows that share a property — for example, comparing each order's revenue against the average revenue for its region.

// EARLIER() — compare each row's revenue against its region's average
-- Calculated column in Orders:
vs Region Avg =
    Orders[Revenue]
    - CALCULATE(
          AVERAGE(Orders[Revenue]),
          Orders[Region] = EARLIER(Orders[Region])
          -- EARLIER() fetches the current row's Region value
          -- from the OUTER row context (the column being computed)
          -- so CALCULATE filters to "same region as this row"
      )
// Row 1001 (North, $1200): North avg = (1200+430)/2 = 815
//   1200 - 815 = +385 (above region average)
// Row 1003 (North, $430):  North avg = 815
//   430 - 815 = -385 (below region average)
// Row 1002 (South, $850):  South avg = (850+95)/2 = 472.5
//   850 - 472.5 = +377.5

EARLIER() — each order vs its region's average revenue

OrderID	Region	Revenue	Region Avg	vs Region Avg
1001	North	1,200	815	+385
1002	South	850	473	+378
1003	North	430	815	-385
1004	South	95	473	-378
1005	West	1,540	1,540	0

West has only one order — its average equals its own revenue, variance = 0 · EARLIER() used in modern DAX is often replaceable with a VAR inside SUMX, which is more readable

Context Summary — The Four Rules

Filter context comes from the report. Slicers, page filters, visual filters, and row/column headings in a matrix all build the filter context that a measure runs inside. A measure cannot see rows filtered out by this context.

Row context comes from calculated columns and iterators. DAX processes each row one at a time, giving the expression access to "this row's" column values. Row context does not restrict what other aggregation functions see.

CALCULATE performs context transition. When called inside row context, CALCULATE converts the current row's values into an equivalent filter context — so aggregations become aware of the current row's attributes without needing explicit filter arguments.

CALCULATE's filter arguments modify the filter context. Each filter argument in CALCULATE either adds a new filter condition, replaces an existing one on the same column, or (with ALL/REMOVEFILTERS) removes filters. The modified context applies only to the expression inside CALCULATE, not to surrounding code.

Teacher's Note: The "row context does not filter" rule is the hardest thing to internalise in DAX, because it contradicts the Excel mental model. In Excel, when you are writing a formula in row 5, you implicitly know you are working with row 5's data. In DAX, a calculated column is in row 5 but SUM() still looks at all rows — until CALCULATE converts row context into filter context. Every time a DAX measure behaves unexpectedly, ask: "Is there a row context I think is filtering, but actually isn't?" Nine times out of ten, adding CALCULATE resolves it.

Practice

Practice 1 of 3

A calculated column formula = SUM(Orders[Revenue]) returns the same grand total value for every row. To make it return the total revenue only for the current row's region, you must wrap it with ___ to trigger a context transition.

Practice 2 of 3

In a measure used for % of Total, the denominator should use CALCULATE with ___(Orders) to ignore the visual's row filters while still respecting the user's slicer selections — so the percentages add up to 100% within whatever the slicer is set to.

Practice 3 of 3

Inside a nested CALCULATE inside a calculated column, the function ___(column) retrieves the column's value from the outer row context — the row currently being processed by the column formula — rather than from any inner context.

Lesson Quiz

Quiz 1 of 3

A table visual has Region on rows and uses the measure Total Revenue = SUM(Orders[Revenue]). What filter context is active when the North row cell is being evaluated?

No filter context — SUM always aggregates all rows in the table and the visual's row grouping only affects how results are displayed, not the underlying data the measure sees The filter context includes Orders[Region] = "North" — the visual's row header automatically adds a filter for that specific region value. SUM therefore only adds up Revenue for North rows, returning 1,630. The Total row has no Region filter, so SUM sees all rows and returns 4,115. The filter context is empty — filter context only comes from slicers and explicit CALCULATE() calls, not from visual row groupings which use row context instead The filter context includes all regions — the visual groups rows after SUM evaluates, so the measure always receives unfiltered data and the visual handles the grouping at display time

Quiz 2 of 3

You create a calculated column Region Total = SUM(Orders[Revenue]). Every row shows 4,115. You fix it to Region Total = CALCULATE(SUM(Orders[Revenue])). Now each North row shows 1,630 and each South row shows 945. Why did adding CALCULATE with no filter arguments change the result?

CALCULATE performs a context transition even with no explicit filter arguments. It converts the active row context — which includes the current row's Region value — into an equivalent filter context. SUM then runs inside that filter context and sees only rows matching the current row's Region, not all rows. CALCULATE with no arguments defaults to using the column's own value as a self-filter — it is equivalent to writing CALCULATE(SUM([Revenue]), Orders[Region] = [Region]) which DAX infers automatically SUM inside a calculated column is evaluated lazily — without CALCULATE, it defers evaluation until the column is fully computed and ends up using a cached grand total; CALCULATE forces immediate evaluation per row CALCULATE resets the filter context to match the column's table filters — without it, SUM inherits the model's default filter which includes all tables; with it, SUM is scoped to the Orders table only

Quiz 3 of 3

A measure Revenue % = DIVIDE(SUM([Revenue]), CALCULATE(SUM([Revenue]), ALL(Orders))) always shows the same percentage regardless of slicer selection — it never adds up to 100% when a region slicer is applied. What should be changed?

Replace DIVIDE with direct division (/) — DIVIDE's fallback argument interacts with ALL() to suppress the slicer-awareness that makes percentages sum to 100% Replace ALL(Orders) with REMOVEFILTERS(Orders[Region]) — removing only the Region filter preserves all other filters and allows the percentages to sum to 100% within each slicer selection Replace ALL(Orders) with ALLSELECTED(Orders) — ALL() removes every filter including slicer filters, so the denominator is always the grand total of all data. ALLSELECTED() removes the visual's row filter but preserves slicer filters, making the denominator equal to whatever the user has selected in the slicer and causing percentages to sum to 100% within that selection. Wrap the entire measure in CALCULATE() — the outer CALCULATE allows the slicer's filter context to propagate into the inner ALL() call, overriding its filter-removal behaviour

Next up — Lesson 36 covers CALCULATE in full depth, including every modifier function (ALL, ALLSELECTED, ALLEXCEPT, KEEPFILTERS, REMOVEFILTERS), how multiple filter arguments interact, and the patterns used to build the most common advanced measures.

← Previous Course Index Next →

Power BI Course