Using Measures instead of Bi-Directional Filtering Relationships to filter Slicers in Power BI

Colin Maitland, 19 December 2019

In this article I demonstrate how to filter slicers using a measure instead of using bi-directional filtering relationships. I will also briefly highlight that the implementation and use of bi-directional filtering relationships between dimensions and facts for the purpose of filtering slicers is not a best practice approach and is in fact an approach that should not be used.

The following image shows part of an Open Opportunities page in a Power BI report. The report is based on a star-schema data model derived from Dynamics 365 Opportunities and related entities. This page contains three slicers; one for Fiscal Quarter/Year (from the Date dimension), one for Territory (from the Territory dimension) and one for Country (from the Customer dimension).

There is an issue with the configuration of the Territory slicer on this page. It is displaying a list of all Territories; not just those that are applicable to the Opportunities for the selected Country. Moving the mouse over the filter icon on the Territory slicer show which filters are currently affecting the slicer:

The relationships between the Date dimension, Territory dimension and Opportunities fact tables are shown in the following image. You will notice that the filtering direction on these relationships is single from the dimension table to the fact table.

A simple potential solution to the issue is to change the filtering direction on the Territory to Opportunities relationship from single to both as shown in the following image.

However, this is not best practice and it is not a practice that should not be used at all for the purpose of filtering slicers. Doing so can potentially cause the results of calculations to be unpredictable; particularly when facts are filtered by the same dimensions using bi-directional filtering relationships, e.g. such as perhaps filtering Opportunities, Orders and Invoices in the same by shared Country, Date, or Territory dimensions.

A best practice solution is as follows:

Create a measure that returns whether there are any Opportunities after taking into consideration all other filters applied to the page; such as those applied from Report Filters, Page Filters and Slicers. This measure returns 1 for true and 0 for false. You could design the measure to return True or False. I have chosen to return 1 or 0 because these are equivalent to True and False but also provide additional flexibility in the usage of the measure, such as the ability to aggregate.

The measure can then be used as a Visualisation Level Filter on the Territory slicer has shown here:

Here is the result when the selected Country is New Zealand. Only Territories applicable to New Zealand (of which there is only one) are displayed in the Territory slicer.

Here is the result when the selected Country is Australia. Only Territories applicable to Australia (of which there is eight) are displayed in the Territory slicer.

You will notice that the filters affecting the Territory slicer include the previously created measure.

In conclusion, measures can be used to filter slicers. Setting the filtering direction on relationships between dimensions and facts should not be used for the purpose of filtering slicers.