Set up dependant slicers with Power BI or Analysis services (tabular)

One of the frequently asked questions by clients working with Power BI and/or analysis services (tabular) is how to get filters or slicers to be dependant on attributes that are not from the same table.

During the evolution of Power BI users got more and better options to set this up. This blog will describe the most common used scenario’s and what the best option is in my opinion.

The case

Consider the datamodel in figure 1. In this case their are 2 dimensions (Product and Store) that are related to a fact table containing Sales data. Both dimensions have a 1-N relationship to the fact table. Notice the arrows on the relationship, both are pointed towards the Sales table. This means that filters applied to both dimensions are pushed through to the Sales table. For example if one product is filtered only the sales for that product is shown. This is the desired results as we want to slice the sales data by describing attributes in the related dimensions.

Looking at the data in the datamodel below we can see that not all products are sold in all stores and that there also is a product that hasn’t been sold anywhere (Foldingbike).

On top of this datamodel a simple report can be created as shown in figure 3. This report page contains two charts that displays sales information by product and store and contains 2 slicers for both ProductName and StoreName.

When one store is selected all products remain visible in the product Slicer but as we can see this store does not sell all products.

For a better end-user experience only products that Bike Amsterdam sells should be visible. Before a solution is implemented it is important to know why this slicer still contains all products. The usual answer I get when I tell people it is because both slicers are not directly related, I am told they are related through the Sales table and that is totally true. The reason why a selected store does not filter the product table is because of how this relationship works. This brings us to the first option.

Option one: changing relationship behaviour

The default 1-N (from Product or Store to Sales with the arrow pointing towards Sales) relationship only pushes filters from the one side to the many side. This is because there is a direct relationship. Selecting one store should filter the sales table down to only that store. But if we want to filter the products after selecting one store, that store filter should be pushed to the products table that does not contain stores. So this store filter must be transformed into a product filter that contains all products that are sold for that one store.

The first and most common used option to get these dependant slicers is by changing the default relationship to a relationship that pushes the applied filters in both directions. This can be done in the relationship view as shown in figure 5.

After changing the Product – Sales relationship to “Both”, under the cross filter direction, the model behaviour changes as follows. When selecting Bike Amsterdam the Sales table is automatically filtered down to only this store. Now this filter is transformed into all products that are sold by Bike Amsterdam and this is pushed to the product table resulting in a situation where the product slicer only shows products that are sold by Bike Amsterdam (shown in figure 6).

This works as expected but this relationship setting should be used with caution. In a data model like this it is safe to use but what if this datamodel is expanded and contains more tables?

Consider the changed datamodel in figure 7. In a situation where selecting both product and store should be dependant on each other both relationships have their cross filter setting changed to both. So selecting a product filters the stores and the other way around.

In the StoreLayout the amount of Square feet per section per store is stored. After adding a new chart to the report we can compare the distribution of space per store (figure 8).

Per store we can see the distribution of space. Now imagine a Power BI report that contains two pages. One page containing sales information per product and store (Figure 3 & 4) and one page that contains the store layout (figure 8).

As a user you are analyzing the sales per product and especially the non-existing sales for the Foldingbike. Because you want to keep track of this product you decide to leave the filter and continue analysis on square feet on the next page.

The chart from figure 8 is now empty as shown in figure 9.

This is due to the fact that the filter direction of the Sales – Store relationship is set to both. And this is the reason that it should be used with caution. I’ll briefly explain.

The slicer on product is set to FoldingBike. The relationship from product to sales (1-N) filters the sales table to only sales on foldingbikes. Because there are none the filter context for the sales table contains no data. Because the relationship between sales and store is set to both the filter on the FoldingBike is transformed into a filter for all stores that sell this bike. Because there are no sales the store table is also filtered down to no records. So there is an empty result for sales and store. Now if we follow the relationship from store to StoreLayout we can see that the filter on the store table (no results) is pushed to the StoreLayout filter (default behaviour from 1-N). Figure 10 shows how relationships are cascaded by changing the cross filter direction. There is no relation between product and storelayout but due to this setting there actually is a relationship.

Option two: custom slicer visual

Another frequently used option to get dependancy on slicers is by using a custom slicer visual. The hierarchyslicer and Smart filter are two visuals that accomplish the desired result. Both work simular and only need the fields to be selected.

Figure 11 shows this slicer with the hierarchyslicer custom visual. In this custom visual the dependancy is shown as a hierarchy that can be collapsed and expanded. It’s important to know that the more dependancy between slicers the deeper the hierarchy gets.

Figure 12 shows this setup with the smart filter custom visual. Slicers are shown seperately but are contained in one visual. Selecting from each slicer is done from a list or dropdown. It has a lot of styling options but some are only in the pro (paid) version.

Option three: default slicers with filter

For a long time Power BI did not support settings filters on slicer visuals. This prevented report creators to base slicers on a subset of data. For example show all attributes besides the Unknown value. This also applies to the case in this blog where all Products should be shown that have sales.

not so long ago this inability to set filters on slicer visuals was fixed. This does not yet work for the new filter pane unfortunately but will probably be implemented soon.

It’s super easy to set up. By selecting the Product slicer look at the filter section as shown in figure 13. By default the attribute that the slicer is based on is shown (ProductName). We are now able to add more filters. This can be all different filter options (Standard, TopN, Relative,..) but also supports measures. By adding the Sum of Amount measure and setting the criteria to “is not blank”. This slicer now only shows ProductNames where the Sum of Amount is not blank.

Looking back at the report (figure 14) we can now see that after selecting Bike Brussel only the product’s that have sales for Bike Brussel remain in the slicer.

This action can be added to the StoreName slicer to get the same functionality in reverse (product filters stores). This third option allows report creators to get dependant slicers without making model changes.

Conclusion

With the ability to set up filters on slicers, report creators can create user friendly dimensions without the need for model changes. All options can be used to accomplish the same result but in my opinion option three is preffered due to it’s simplicity and also that there is no need for custom visuals (that might be disabled in your organisation).