Cascading Quick Filters in Tableau

Overview

This blog post is going to cover both the basics of quick filters as well as trying to push the boundaries, so if you know the basics, feel free to skip down to the later examples!

Quick Filters refresh

Before I get into the ins-and-outs of cascading quick filters, I should probably put a quick explanation of what a “Quick Filter” is. What Tableau defines as a Quick Filter is just a specific visual object for allowing easy alteration of filters on the visualisation. The visual appearance can be altered if desired, and the quick filter can be surfaced on a dashboard to allow an end user to dynamically alter the filter, making a single dashboard the equivalent of many static ones.

“Cascading” filters?

Cascading (or hierarchical) filters are ones where the selection on the first filter causes the second to be limited to only those values that are now relevant. This sort of set-up is vital in preventing a situation where the user has selected to see all data for the 30th day of the month of February, for instance. It creates a better experience for the user, so let’s go over the basics of how it’s done, then try to push it as far as we can!

Note, I will not be discussing the effect of quick filters on performance in this post

[EDIT: As pointed out by Jonathan Drummey in the comments, whilst I am not discussing performance, it is important to note that many Quick Filters can seriously damage the performance of a dashboard, so proceed with caution!]

 

Linking 2 quick filters in Tableau

Add the filters to the view

Quick filters are added to a view by right-clicking on a field and selecting “Show Quick Filter”. Note that the field doesn’t have to be in use in the view at the time.

Do this for a couple of fields – in the example workbook at the end of this post I’ve used “Zone”, a calculated field that separates the regions into 3 zones, and Region.

Link the filters

Looking at the properties of the quick filter for Zone, we can see that we can two options: “All values in Database” or “Only Relevant Values”.

All Values in Database is exactly what it sounds like – this will fill the quick filter list with all values for that field within the datasource.

Only Relevant Values is a little more subtle. This means the quick filter list will show all the values that are left after applying the other filters, if those filters are on the same or a higher level in the hierarchy.

This means that if the Zone quick filter is set to All Values in Database and the Region quick filter is set to Only Relevant Values, we get a cascade of 2.

The power of 3

To add a 3rd quick filter requires introducing the concept of “Context”. I don’t have the space here to go into how it works, but in essence it is a primary filter layer.

A field can be added to context if the field is already on the Filters shelf.

Adding the 3rd quick filter (in my example, State) to the view requires adding the top level,“Zone”, to context. Then Region is altered to “All Values in context” (whatever the “Context” set of filters lets through), and State is set to Relevant.

Context Filters, party of 4?

3 is good, but let’s keep going with number 4. For this one, we need to take note of something about the “Only Relevant Values” setting. As mentioned before, it applies a filter based on the same or a higher level in the hierarchy. As Context works as another master level, we can add a field to context, and have its Quick Filter set to Only Relevant to add a sub-level within the context.

In my example, I have added City as a quick filter, with both Zone and Region added to Context.

The 5th Element

Obviously, since we have got this far, we have to try to go further. Unfortunately, there are no more options in terms of sub-layers within Context, and no other settings on the quick filters, so that would appear to be it.

Except…

there is a way round this problem based on the order that Tableau calculates filters.

Context filters are first, then dimensions, then measures, and then table calculations.

As we have filled up context and standard dimensions, we could look at measures. However, that allows filtering on a range of aggregated values, which is not normally what is required for cascading filters, so I have discounted this step.

That leaves table calculations!

To turn a dimension into a table calculation we need to use the LOOKUP trick.

Adding this to the view gives us what is effectively an “Only Relative” quick filter, and so the 5th filter.

[EDIT: Number 6 added with help from Jonathan Drummey]

Number 6 – let’s go back to the Source

As mentioned by Jonathan in the comments, there is a way of adding filter number six, as long as you are rocking version 8 of Tableau. (really, why wouldn’t you be?!) – Data Source Filters.

Now, I know what you are thinking – you can’t use one of these as a quick filter. And you’d be right, but that’s not stopped us so far, so let’s see what we can do with this.

The aim is to present a selection option for the end user, without using a quick filter, and so we turn to parameters. Parameters can be surfaced to the end user, but don’t do anything in an of themselves – we need to combine them with a calculated field.

For my parameter I created a list of 2 Territory Managers, and then used a calculated field to check whether the Zone is valid for the selected Manager.

This is then added as a data source filter (note that in the Tableau Public version of this file, there are two data sources, as I only wanted this filter to apply to one view – same setup as the old-school Global Filter)

Choose to “Show Parameter Control” and filter(ish) is now complete.

A couple of last things regarding datasource filters:

1) Terminology. As we now have a data source filter in place, the previous setting of “All values in Database” technically becomes “All values after datasource filter”

2) Is this a true equivalent of a quick filter? Well, no – not quite. Currently there is no way to make the parameter values update automatically, so this is a fixed list. However, as this is the top-most filter, that’s ok.

[/EDIT]

But what if I want more?

Can we go further? Not really, no. To get more levels, you have two options, use a measure (already discounted), or put a dimension on the Pages shelf.

The pages shelf has potential, but is much more specific in the set-up you need. The layout is calculated before this step, so axes won’t change size, and headers won’t be hidden if the specific page has no data. The upshot of this is that using the Pages shelf in this way would likely cause more problems than it solves.

 

And there you have it. I’ve included a summary table and an example workbook below, so let me know if you found this useful, or if has only sparked more questions!