When Single Directional Filtering Appears Not to Work
One of the major factors that underpins a good Power BI data model is when filters only propagate from the one side of a relationship to the many, that is from dimension tables to the fact table. This is known as single-directional filtering. However, even when such a model has been designed, it may appear that filters are in fact, passing from the fact table back up to a dimension. It may appear that when you filter the fact table, a dimension will respond to this filter as if the filter has propagated from the many side of the relationship to the one side.
Let’s take an example. Let’s assume we have a Sales fact table that has a Customers dimension related to it via the CUSTOMERID columns in both tables: -
We have a measure that sums the QUANTITY column in the Sales table:-
Total Qty = SUM(Sales[QUANTITY])
We then create a table visual that holds the CUSTOMERID column from the Sales fact table and the Total Qty measure. A slicer is then populated with the CUSTOMERID column also from the Sales table. We have also created a table visual that holds the CUSTOMERID field from the Customers table, again with the Total Qty measure:-
If we now use the slicer to filter CUSTOMERID 4, the CUSTOMERID column from the Sales fact table is filtered accordingly, which we would expect, but the slicer also appears to filter the CUSTOMERID field from the Customers dimension which we would not expect because filters don’t pass from the many side of a relationship to the one side:-
However, what we are looking at here is simply an illusion. The Customers dimension is not being filtered at all. Because the fact table has been filtered to just CUSTOMERID 4’s sales there are no values for the Total Qty measure for any other customer and by default items with no values don’t show in visuals. If we turn on “Show items with no data” on the table visual that holds the CUSTOMERID from the Customers dimension, we can see that this is true:
Therefore, we can see that the filter from the Sales fact table is not being propagated to the dimension. It’s just because where there are no calculations to show in the table visual, those items are not displayed by default. You can see that there is a difference if we have a slicer that uses the CUSTOMERID field from the Customers dimension. Even with “Show items with no data” still turned on, when we filter CUSTOMERID 4 from this slicer, this filter is propagated to the fact table and so both visuals filter accordingly:-
Suppose you wanted to ensure that filtering the Sales fact table directly did not appear to filter the Customers dimension. In that case, there must always be values to show in the table that uses the Customers dimension. Therefore this measure would do that job:-
Total Qty v2 =
CALCULATE (
[Total Qty],
ALL ( Sales[CUSTOMERID] ),
VALUES ( Customers[CUSTOMERID] )
)
Unless you are using bi-directional filtering (not to be recommended), filters will only propagate from dimension tables to fact tables. However, always keep in mind that by default blank values are never displayed in visuals unless you turn on “show items with no data” and this may give the illusion of a visual being filtered.
Add new comment