Understanding Row and Filter Context

Every DAX calculation runs inside a context — a definition of which rows are relevant at the moment of evaluation. Without understanding context, it is impossible to predict what a measure will return or to diagnose why it returns the wrong value.

Row Context

Row context is the context that exists inside a calculated column. When Power Pivot evaluates a calculated column, it processes each row individually — DAX knows exactly which row it is on and uses only the values from that row in the calculation.

What it is? The awareness of the current row. The formula can reference any column in the same table and receive the value for that specific row — not a sum or average, but the exact cell value.

Filter Context

Filter context is the context that exists when a measure evaluates. It is the complete set of active filters on the data model at the moment the measure calculates — determined by everything the pivot table currently knows about that specific cell.

Every cell in a pivot table has its own unique combination of active filters.

A blank cell does not indicate an error — it means the filter context for that cell returns zero matching rows. Under the current combination of active filters, there is simply no data.

The Four Sources of Filter Context

Filter context accumulates from four independent sources simultaneously. Each one narrows the dataset further:

1. Row labels: each value in the rows area applies a filter to its row. Region = North and Region = South create different filter contexts — every cell in the North row sees only North sales;
2. Column labels: each value in the columns area adds a second filter dimension. Combined with row labels, every cell now sits at the intersection of two independent filters;
3. Slicers: a slicer selection applies its filter to every cell in the pivot table simultaneously. Selecting January from a month slicer restricts all cells to January data regardless of their row or column position;
4. Pivot table filters: filters applied directly in the pivot table filter area also contribute to the filter context of every cell, stacking on top of any row, column, and slicer filters already active.

Task

Step 1 — Row context experiment

• Open the workbook. Go to Power Pivot → Manage → Data View → Sales tab.

• Look at the Order Size calculated column. It contains either Large or Small for each row, based on the Quantity value in that row.

• Now add a second calculated column to the Sales table using the following formula:

= Sales[Quantity] * Sales[UnitPrice]

• Rename it Row Revenue.

• Scroll through the column and confirm that each row shows a different value, namely the product of that specific row's Quantity and UnitPrice.

Step 2 — Then answer the following questions

1. What type of context is being used when DAX evaluates Row Revenue for each row?
2. Is Row Revenue the same as the Total column already in the Sales table?
3. Would it make sense to create Row Revenue as a measure instead of a calculated column?