For Power BI version 2.76.5678.782 (December 2019).

Understanding filter context is key when working with Power BI. Filter context and context propagation are important concepts we have to understand, if we really want to understand DAX.

Initial filter context

Initial filter context is a filter that comes from visualizations or other parts of reports. Let’s look at simple example. We have Products table with fields Platform and Storage.

We create a Matrix visualization and select the field Platform to Rows and field Storage to Values.

We get Count of Storage for every Platform. What happened? Every cell inside a table is filtered. First cell causes the table Products to filter, only leaving rows with Platform value PC. Now all the values in Storage column are summed up to the value 107. Similarly the next cell filter table Products, leaving only rows with Platform value PS3. Column Storage is then summed up, returning value 7.

Filtering happens in the moment of evaluating the cell and is then released.

Sources of filter context

Filter context can come from different parts of a report. We can split them up into four categories:

1. Rows
2. Columns
3. Slicers
4. Filters

Instead of slicers we could use any other visualizations, since every visualization filters all other visualizations.

Initial filter context comes from the 4 groups above. We call it initial because it can later be changed using DAX formula CALCULATE(). Initial filter context always means filtering from upper 4 sources, before filtering with CALCULATE() formula.

Filter propagation

Let’s look at a data model that is comprised of dimension tables Products and Projects and fact table Sales.

In report view we create a following matrix.

Column Count of Storage remains the same as in the example above. But surprisingly column Count of Project has the same value for all platforms. Why? The reason is the row PC or the Platform field only filters the table in which it is located. Because it is located in the table Products it only filters table Products. Table Projects remains unfiltered and returns count of all products for each platform.

Filter flow between tables

Let’s add another column to the table above: Value field from table Sales. We get the following table.

Even though Sales table contains Value column, table remains unfiltered. Filters flow between tables, but only in the direction of connection.

With typical layout of tables (dimension tables above, fact table below) filters always travel down.