Tableau: filtering a filter

by Ali Agah

Quick filters are potentially the most popular and useful way to add interactivity to your dashboard. But you may have noticed a problem when you use more than one for a worksheet: filter values are independent.

If you want your filters to filter other filters, things get complicated...

Spinning Dream
Here is the required Inception reference. Photo by Christophe Hautier / Unsplash

For example, when I filter the Product Category in the table below (using Sample Superstore), the Product Sub-category filter remains unchanged. The relevant values untick when I click on the filter but I can tick them again even though they don't do anything anymore.

Fig1. Using 'All Values in Database'

As you can see, this doesn't change even if the filter upstream is in context.

Filters normally default to using "All Values in Database" (see Fig1) which makes them independent of other filters. They present all values from the field regardless of what it would do to the view.

How can we only make the Sub-Category filter show only sub-categories that are within the Category selected?

Cascading filters

Connecting filters in this way is called cascading filter. Much like the waterfall in Fig2, anything that does not make it past the first level (i.e. Category filter) will not be available in the second level (i.e. Sub-Category filter) and so on...

Twins Waterfall located in Ituporanga south Brazil. Just sit on the passing water and enjoy.
Fig2. waterfall analogy Photo by Luis Fernando Felipe Alves / Unsplash

There are two ways to make cascade filters:

1) "All Values in Hierarchy"

If the values are hierarchal by nature, you have the benefit of creating a hierarchy, clicking the carrot on the filter and choosing "All Values in Hierarchy" (see Fig3).

Fig3. Using "All Values in Hierarchy"

However, this only works well if values are hierarchal, preferably strictly so like Table 1 in Fig4 below. But things break down if we introduce another element like state (see Table 2 in Fig4). Having state in the product hierarchy would be both confusing and dangerous for our other worksheets.

Fig4. Hierarchal and non-hierarchal tables.

2) "Only Relevant Values"

To solve the issue with "All Values in Hierarchy" we can ask Tableau to be a tad more clever.

Click the carrot on your filter and choose "Only Relevant Values" (see fig.5) to inform Tableau that you only want values in the filter that correspond to values selected in other filters.

This can be done in multiple levels but, like inception, there is a risk of being trapped in limbo!

Fig5. Using "Only Relevant Values"

We need to be really careful when using multiple filters. Unlike the "All Values in Hierarchy" option, Tableau does not know how the filters relate to each other nor how you want to use them.

This means the relationship between the filters can get very confusing very quickly. See Fig6 as an example.

Fig6. Confusing filters.

One solution could be to guide the user by numbering the filters in the order they need to be used. But it still doesn't take much to break your dashboard.

A more robust solution is to use a mixture of filter cascades to compensate for the logic. The table below is a helpful guide on how to configure your filters as you add more of them. I recommend Sijia's blog or this TIL blog post for more on this.

Fig7. Configuring multiple cascading filters.

Enjoy vizzing!

© 2022 The Information Lab Ltd. All rights reserved.