Default slicer interaction

Have you ever been annoyed that your Power BI slicers don't appear to interact with each other by default? Take the report below for example, which shows a list of films with slicers to filter by director and genre:

After selecting Akira Kurosawa from the Director slicer, I still have the option to select Awful films from the Genre slicer. Surely, this can't be right?!

This means that you can select combinations of options from multiple slicers which produce no results:

As I thought: Kurosawa didn't make a bad film in his career!

Wouldn't it be more intuitive if the Genre slicer was filtered based on the choice you make in the Director slicer?

Why does it work this way?

The reason for the behaviour shown above is that the relationships in the data model only allow filters to propagate in a single direction by default. You can see this in the model view of your report:

The arrow on a relationship shows in which direction a filter can travel.

In the diagram above, the direction of the arrows show that a filter can flow from the Director table to the Film table, but not from the Film table to the Genre table. Currently, the filter applied by selecting a Director in the slicer has no way to reach the Genre table.

Editing relationships

One way to solve the problem is to change the filter direction of a relationship to allow filters to flow in both directions. For our example to work, we need to allow filters to flow from the Film table to the Genre table.

To do this, click on the relationship whose direction you want to change, then change the Cross-filter direction option in the Properties window:

Set the Cross-filter direction to Both then click Apply changes.

After doing this, the arrow should indicate that filters can now flow in both directions:

The arrows show that filters can flow in both directions in this relationship.

Returning to the report view should show that the Director slicer now affects the Genre slicer:

Selecting a director now shows only the genres of films made by that director.

Problem solved! Or is it?

What's the problem?

If changing the filter direction of a relationship to Both solves the problem so easily, it begs the question: why isn't it the default? The simple answer is that allowing filters to flow in both directions for every relationship in a complex data model can cause more significant problems than slicers not filtering each other!

For the purposes of this blog, it's sufficient to know that allowing filters to flow in both directions can cause problems that are difficult to solve later.

Creating a measure

If allowing bi-directional filtering is, in principle a bad idea, what other option do we have to make our slicers interact with each other? One option is to use a measure which allows us to filter out the genres which have no films by the selected director.

To create a measure, select the table you want to add the measure to and choose Home | New measure from the ribbon. You can enter the code below to create a measure which counts the rows of the Film table:

The COUNTROWS function counts the rows of the specified table.

To picture the results of this measure, you can place it into a table visual along with the Genre field:

Selecting a director shows how many films of each genre they directed.

Where a director has no films for a particular genre, the result of the measure is Blank.

Filtering a slicer

We don't really want to display the results of the measure in a visual. Instead, we want to use it to apply a filter to a slicer.

To do this, select the slicer you want to filter, then drag the measure into the Filters on this visual part of the Filter pane.

We have selected the Genre slicer then dragged the Count of Films measure into the Filters on this visual area of the Filters pane.

You can then set the filter to show only items where the value of the measure is not blank:

Choose to show items when the value is not blank then click Apply filter.

You should now find that the slicers behave in the same way as though you had modified the filter direction of the relationships:

The Genre slicer now responds to selections made in the Director slicer.