Power BI / Intermediate: context, quality and access / Lesson 8

Iterators, row context and related data

Explain row-by-row expressions and relationship lookups without assuming a current row automatically filters every table.

About 5 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish row context from filter context.
Use RELATED only under a valid relationship contract.
Recognize context transition when model measures are evaluated.

Name the row being iterated

An iterator such as SUMX evaluates an expression for each row of its table argument and aggregates the results. That current row provides row context, allowing Quantity and UnitPrice from the same sales line to be multiplied. Filter context instead determines the data visible to the calculation. The two contexts can coexist and should not share a vague label such as current selection. State which table is being iterated and which filters already restrict it. Iterating products and iterating sales lines create different row contexts even when both ultimately contribute to the same revenue total.

Assume DimProduct has unique keys A and B with synthetic unit costs6 and12 and the active many-to-one relationship from each sales row. RELATED can retrieve the corresponding dimension attribute from a row context. The first measure sums quantity times unit margin and is intended to return28 for the shared fixture. The relationship must have the expected cardinality and supported evaluation context. A missing or duplicate dimension key is not solved by choosing any convenient cost. Decide historical cost treatment too: a current product cost is not automatically the cost that applied when an old sale occurred.

dax Row-grain profit with a related attribute

Gross Profit =
SUMX(
    FactSales,
    FactSales[Quantity] * (FactSales[UnitPrice] - RELATED(DimProduct[UnitCost]))
)

Assume the documented three lines, unique product keys and costs A6/B12. Expected contributions are8,8,12 and total28. DAX was not executed.

Understand context transition

A row context alone does not generally make a raw aggregation on another table respect that row as a filter. CALCULATE can transition row context to filter context; evaluating a model measure within an iterator performs the relevant transition automatically. This explains why iterating DimProduct and summing a raw sales aggregation can repeat the grand total, while evaluating the Revenue measure can give per-product results under the relationship. Do not memorize a wrapper without understanding the table and desired context. A transition can also apply more row values than a reviewer expects, so unique keys and a small trace matter.

Trace an independent profit oracle

The second program performs the same business arithmetic using an explicit cost dictionary. It makes row contributions visible before the final total. The program neither evaluates RELATED nor tests context transition; it validates the expected amounts for a known key mapping. That limitation is useful: if Power BI later differs from28, investigate data, relationship and context before changing the business formula. Keep the oracle’s cost assumptions aligned with the intended model. If costs change historically, a simple dictionary of current costs is no longer a valid expected-result source for all past periods.

python Synthetic line-profit oracle

cost = {"A": 6, "B": 12}
lines = [("A", 2, 10), ("B", 1, 20), ("A", 3, 10)]
profit = [quantity * (price - cost[product]) for product, quantity, price in lines]
print(profit)
print(sum(profit))

Expected output

[8, 8, 12]
28

The list exposes8,8,12 and the total28. It reads only in-memory public fixtures and is not a DAX-engine test.

Review iterator cost and meaning

An iterator is not automatically wrong or slow; its table size, expression and storage mode matter. Begin with semantic correctness, then assess engine evidence in an authorized small model. Avoid expanding a fact table unnecessarily when a simpler aggregation already represents the metric. Test one product, the full fixture, an unmatched key policy and a historical-cost case. This course runs the profit arithmetic only and makes no DAX performance claim. A rigorous explanation names row context, filter context, lookup relationship and transition explicitly, so a correct total is not mistaken for proof that every intermediate context was intended.

Common errors and fixes

The grand total repeats once per iterated product

Why it happens: A raw aggregation was expected to use row context as a filter.
How to fix it: Identify the required transition and evaluate the intended measure/context.
Prevent it next time: Trace a two-product example before extending the iterator.

Current unit costs rewrite historical profit meaning

Why it happens: The lookup attribute’s time meaning was unspecified.
How to fix it: Use the approved historical-cost grain or document the current-cost scenario.
Prevent it next time: Include cost-effective dates in the metric contract when required.

RELATED is blamed for duplicate dimension keys

Why it happens: The relationship and source-key contract were not validated.
How to fix it: Repair dimensional uniqueness and missing-key handling deliberately.
Prevent it next time: Audit keys before writing lookup-based calculations.

Check your understanding

1. Why does a current sales row not automatically filter every table in a raw expression?

Show worked solution

Row context supplies current-row values; filter context determines visible data. Relationship lookup and context transition are distinct mechanisms with explicit rules. State the iterated table and intended result, then use the appropriate measure or transition. Adding CALCULATE everywhere without that model can conceal another mistake instead of explaining the calculation.

2. The profit oracle gives28 using current costs. Can it validate historical profit for all dates?