Row Context and Filter Context Walk Into a Tea Stall

It’s a Monday morning. The kettle hisses. The fan turns slowly above the counter. Reena has the laptop open and, for the first time, a model she is proud of.

The Sales table is the one she built the hard way: three monthly files appended into 1,248 rows, then the Products lookup merged on, so every row now carries Date, Bill No, Product ID, Quantity, Unit Price, Amount, Category and Unit Cost.

Her cousin wants two things on Sunday. Total revenue, and revenue broken down by category and month.

She writes the first formula as a measure, exactly the way it reads in English:

Revenue Check = Sales[Quantity] * Sales[Unit Price]

Power BI refuses to accept it at all:

A single value for column ‘Quantity’ in table ‘Sales’ cannot be determined.

Reena says, out loud, “There are 1,248 values for Quantity. That is the entire point of a table.”

So she tries the other way round. She adds a calculated column instead:

Month Revenue = SUM(Sales[Amount])

This one is accepted without complaint. She scrolls the column. Every row reads ₹486,200. Row 14 reads ₹486,200. Row 900 reads ₹486,200. She drops it into a visual, clicks a slicer, and the number does not move.

Her phone buzzes. The cousin, late as always: “One formula can’t see the table. The other can’t see the report. You’re missing a context each time.”

She mutters, “What is a context?”

The awning rustles.

A man steps in under the awning — narrow, precise, indigo shirt, moving like someone who does not want to lose his place. He is carrying a long metal ruler with a single slot cut into it, about the height of one line of writing. He lays it flat across Reena’s printed table, and through the slot exactly one row shows.

He says, “I am Row Context. I am the answer to the question which row are we on. Through my slot you can see row 14, and you can see every column of it — Quantity 3, Unit Price ₹40, Amount ₹120, Category Biscuits. Ask me for Sales[Quantity] and I hand you 3, because here, right now, there is exactly one.”

He slides the ruler down one line. “Now Sales[Quantity] is a different number. I move. That is all I do.”

A woman walks in behind him — quick, bright, magenta shirt, carrying a flat case of translucent coloured sheets, the kind used over a stage light. She spreads Reena’s table out and starts laying sheets on top of it. One sheet is printed Category = Biscuits. Another is printed Month = March. Where they overlap, a small clear patch is left.

She says, “I am Filter Context. I have no idea which row we are on and I never will. I do not look at rows one at a time. I decide which rows are still in play. Right now, 213 of your 1,248 rows are under the clear patch. The rest are covered.”

Reena looks at the two of them. “So one of you sees a single row, and the other sees a crowd.”

They reply, together, “And neither of us shows up uninvited.”

Row Context speaks first

Row Context keeps the ruler where it is.

He says, “A calculated column is mine. When you add one, Power BI walks your table from top to bottom, and on every one of the 1,248 trips I am there, holding the slot over the current row. That is why Sales[Quantity] * Sales[Unit Price] works perfectly in a calculated column — on each trip there is exactly one Quantity and exactly one Unit Price.”

Reena says, “Then why did it fail as a measure?”

“Because a measure is not walked down the table. A measure is evaluated once, for a cell in a visual. There is no trip. There is no current row. I am simply not there. So when you write Sales[Quantity] with nobody holding the slot, Power BI looks at 1,248 values and asks you which one you meant. That is the whole of that error message.”

He sets the ruler down. “And here is the part worth remembering, because it is the fix. I can be created on demand. Functions ending in X — SUMX, AVERAGEX, MAXX — are iterators. They take a table, walk it row by row, and hold my slot open while they do it.”

Reena writes:

Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

It is accepted. It returns ₹486,200.

Reena says, “Same formula as before. The only difference is that something is walking the table.”

“The only difference is that I exist. SUM wants one column handed to it. SUMX walks, multiplies on each row, and adds up the results. SUM(Sales[Amount]) is a shortcut for exactly that, on a column you already stored.”

He adds, in fairness, “Do not read this as use the X version always. When a plain SUM will do, use it — it is the faster path. Reach for me when the thing you need does not exist as a stored column, like Quantity times Price.”

Filter Context speaks

Filter Context taps the clear patch in her stack of sheets.

She says, “Now your second formula. SUM(Sales[Amount]) inside a calculated column returned ₹486,200 on all 1,248 rows, and you thought it was broken. It was not broken. It was honest.”

Reena says, “It gave the grand total on a row about one packet of biscuits.”

“Because SUM does not care which row you are on. It aggregates whatever rows are in play, and inside a calculated column, nothing has filtered anything. Every row is in play. So it added up all 1,248 amounts, 1,248 separate times, and wrote the same grand total in every cell. Your calculated column was computed once, at refresh, in an empty room. I was not there.”

She lays the sheets back down.

“Put the same formula in a measure and I arrive. I come from the report itself, and I come from several places at once: the row the cell sits on, the column the cell sits under, every slicer on the page, every page-level and visual-level filter, and anything your cousin clicks on a chart.”

She points at Reena’s matrix. “The measure Revenue = SUM(Sales[Amount]) is one formula. In the Biscuits row under March, I hand it 213 rows and it returns ₹41,300. In Tea under January, I hand it a different set and it returns something else. One formula, a different answer in every cell, because I am different in every cell.”

Reena says, “That’s why the calculated column ignored my slicer.”

“Your calculated column was finished and stored before the slicer existed. A measure is computed after the click. That is the entire difference in behaviour, and it follows from which of us is in the room.”

She adds, “One more thing, since your model has relationships now. I travel. Filter a Products row on the one side and I follow the relationship down to the many side of Sales. Row Context does not travel — he stays in the table he was given.”

What just happened

Reena pours herself a glass of tea, sits down on her own counter, and writes the grid out.

She looks at the last row for a while. “Both of my formulas failed for the same reason. Each one was written as if the other kind of context was there.”

The same chat, in a chart

That picture is the same conversation, drawn. The first panel is the slot: one row, all of its columns, a formula that can multiply two of them together. The second panel is the stack of sheets: no row singled out, just a surviving set, and one formula that answers differently in every cell because the sheets change.

One last warning before they leave

Filter Context gathers her sheets. She says, “Two traps. The first one is the famous one, and it is the moment the two of us touch.”

She says, “Put a measure inside a calculated column. Like this.”

Reena types it:

Row Revenue = [Revenue]

She scrolls. Row 14 reads ₹120. Not ₹486,200.

Reena stares. “It’s the same measure. In a visual it gives me totals. Here it gives me one row’s amount.”

Row Context says, “Because the moment you reference a measure, DAX wraps it in CALCULATE, and CALCULATE does one thing people forget: it converts me into her. My slot, holding row 14, becomes a filter — Date is this, Bill No is this, Product ID is this, every column of that row. She then aggregates the rows that survive, which is that one row. The effect has a name: context transition.”

He adds, “So SUM(Sales[Amount]) in a calculated column gives the grand total, and [Revenue] in the same calculated column gives the row’s own amount, even though Revenue is defined as SUM(Sales[Amount]). Same formula. One of them went through CALCULATE and the other did not. This single fact explains most DAX answers that look impossible.”

Filter Context says, “Two, and it is the boring one that costs you money. A calculated column is computed at refresh and stored in the model, once per row. 1,248 rows is nothing. 50 million rows is a column that sits in memory forever, refreshes slower every month, and still cannot respond to a slicer. Measures store nothing and are computed on the click.”

She nods at the awning. Through it, at the far end of the counter, Calculated Column and Measure are sitting with their clipboard and their calculator, exactly where they were the last time.

Measure raises his glass. “We told her filter context months ago. We never said what it was.”

Reena writes it down. Columns get rows. Measures get filters. CALCULATE turns one into the other.

The bill

They left the way people leave a tea stall on a working morning. Row Context slid his ruler into a long sleeve and went first. Filter Context stacked her coloured sheets back into their case, held one up to the light on the way out, and put it away.

Reena deleted the Month Revenue column. She kept Revenue = SUM(Sales[Amount]) as a measure, added Margin = [Revenue] - SUMX(Sales, Sales[Quantity] * Sales[Unit Cost]), and dropped both into the matrix. Category down the side, month across the top. Every cell answered for itself. The slicer moved all of them at once.

Her cousin, on Sunday, clicked the slicer a few times and asked the only question he ever asks when something works: “Why is this one fast?”

Reena said, “Because nothing is stored. It’s all computed when you click.”

She wrote the sentence she wanted to keep at the top of the page:

Row context knows which row. Filter context knows which rows. A formula fails when it assumes the one it does not have.

For the math-curious

The two contexts, formally. An expression in DAX is evaluated in an evaluation context made of two independent parts. Row context binds column references to a single row — created by calculated columns, by calculated-table expressions, and by iterator functions. Filter context is a set of filters over the model that determines which rows remain visible — created by visuals, slicers, report filters, CALCULATE arguments, and filter propagation across relationships. A bare column reference needs row context. An aggregation reads filter context.

Iterators. SUMX, AVERAGEX, MINX, MAXX, COUNTX, RANKX, FILTER and ADDCOLUMNS all take a table as their first argument and create row context over it:

Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

SUM(Sales[Amount]) is internally SUMX(Sales, Sales[Amount]). Nested iterators create nested row contexts; the inner one shadows the outer for the same table, which is what the legacy EARLIER function existed to reach past. Use VAR instead — it is readable and it does not depend on nesting depth.

Context transition. CALCULATE converts the current row context into an equivalent filter context on every column of that row, then applies its own filter arguments. Any measure reference is implicitly wrapped in CALCULATE, so [Revenue] inside a calculated column transitions. One consequence catches people out: if two rows are identical across all columns, transition filters to both, and the result is their sum rather than one row’s value. A unique key column prevents this.

Modifying filter context. CALCULATE(expression, filter1, filter2, ...) replaces filters on the columns it touches. ALL removes them, ALLSELECTED respects outer selections, REMOVEFILTERS is the modern spelling of ALL as a modifier, and KEEPFILTERS intersects rather than overwrites. The classic share-of-total is:

Share = DIVIDE([Revenue], CALCULATE([Revenue], ALL(Sales[Category])))

Relationships. Filter context propagates along relationships, by default from the one side to the many side — see the one-to-many post for why the direction matters. Row context does not propagate; to read a related row from inside row context you need RELATED (many side to one side) or RELATEDTABLE (one side to many), and RELATEDTABLE is itself CALCULATETABLE, so it transitions.

One formula, a different answer in every cell. That is not the formula changing. That is the context changing underneath it.