#### September 25, 2020

## Context Transition in Power BI

*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:

- Rows (#1)
- Columns (#2)
- Filters (#3)
- 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.