For Power BI version 2.76.5678.782 (December 2019).

Filter context, row context and context transition from row to filter context are most challenging topics when learning DAX. We have already talked about filter and row context in previous articles Filter Context in Power BI and Row Context in Power BI. Here we’ll show how we transition from row context to filter context.

Filter and row context

Filter context is any filtering that comes from report visualizations. We separate filter sources into four groups:

1. Rows (#1)
2. Columns (#2)
3. Filters (#3)
4. Slicers (#4)

For every cell of matrix the tables are filtered in the model according to columns name, row and all additional filters, for example values selected in the slicer. Filtered table in the model is then aggregated into a single result, that is printed in the cell of the matrix.

Filter affects all the tables in the data model. If tables are connected filter flows down in the direction of the connection. With usual positioning of the tables (dimension table high and the fact table below) we say filter flows downward.

Row context is property of being aware of rows. Row context means function is applied to every row separately. Expression of the function is calculated for each row and then aggregated into one final result value.

Row context is the property of:

• X-functions, for example SUMX(), AVERAGEX()
• Function FILTER()
• Calculated columns

Let’s look at example using the function SUMX.

Function SUMX() creates a row context in the table Sales (#1). It then iterates through the table, row by row. In each row it takes the value of columns Price (#2) and Tax (#3) and sums them up. In the end, it sums up all the sums into one result value (#4).

Row context doesn’t always create filter context

Like we saw in the Row context in Power BI article, row context doesn’t always create a filter context. Let’s again look at the following example.

We add calculated column to the Products table: TotalSales = SUM(Sales[Value]). Column will sum up all the values in the Value column of the Sales table. What do we expect as a result? Will result differ for each product? The answer is no, result will be the same for all rows.

The reason here is: row context doesn’t always create filter context. Value is calculated for each row separately, but product in the row can’t filter the Sales table. Sales table remains completely unfiltered and the result is the same for each row.

Transition from row to filter context: CALCULATE()

We can still create row context in the example above. We call this context transition. All we need to do is to use a CALCULATE() function. Let’s try to add column like before, only in this case we also use CALCULATE() function. We get the following result.

Function CALCULATE() “turns on” the filter. This means filter from the Products table will now also filter the Sales table according to the product in each line and return the result accordingly.

Context transition exists in:

• X-functions, for example SUMX(), AVERAGEX()
• Function FILTER()
• Calculated columns

Implicit CALCULATE() in measures

Every measure actually contains a hidden implicit CALCULATE(). Let’s look at what this means through an example. We’ll create a measure using the same formula as we used for calculated column.

We follow the steps bellow.

1. Right click the Products table and select New measure.

2. Enter the formula TotalSales_def = SUM(Sales[Value]).

3. We use the measure in a new column. Select New Column and enter TotalSales_Measure = [TotalSales_def].

We get an identical column of the TotalSales_CALCULATE column. We would expect the column to be the same as TotalSales, since we didn’t use CALCULATE() function in the measure. But CALCULATE() is hidden and implicit in every measure, we just can’t see it. The lesson again is: measures already contain the CALCULATE() function.