<< Back

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-wtarfallCascading (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.

image

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”.

image

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.

imageimage

imageimage

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.

image

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.

imageimage

imageimage

imageimage

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.

imageimage

imageimage

imageimage

imageimage

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.

image

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.

image

image

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)

image

image

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!

Quick Filter 2 filters 3 filters 4 filters 5 filters 6 filters
1st All Values All values All values All values Parameter + Data Source filter
2nd Only Relevant All in context Only Relevant Only Relevant All values
3rd Only Relevant All in context All in context Only Relevant
4th Only Relevant Only Relevant All in context
5th Table Calculation Only Relevant
6th Table Calculation
Context Fields 1st 1st, 2nd 1st, 2nd 2nd, 3rd

13 thoughts on “Cascading Quick Filters in Tableau

  1. Craig,

    For the 5th filter, your table calculation when I click Edit to view says it will calculate Table (Across), but then in the Filter shelf I see that it is calculating Table (Down). I believe this step is not working for me because I am unable to get my table calculation to calculate Table (Down) when I add it to the Filter shelf and Table (Down) is not an option when I try to edit it. Could you please tell me if there was something you did to get the calculate to work Table (Down)?

    Thank you,
    Erika Rigal

  2. Great article which covers pretty much everything you need to know about Tableau Filters.

    Here is the problem though:

    IT DOESN’T WORK BACKWARDS. i.e.If I choose state at level 3 and then change any of the level 1 or level 2 values I will end up with blank view till I choose some options on the filter shelf.

    Tableau still doesn’t have any “refresh all filters” or “Filter’s default value” functionality.

    PS: Why you don’t just use “only relevant values” option on 5+ filters. This works for me (I’m on 9+ version ofc). May be I don’t see some issues with this approach?

    Thanks in advance,
    Ivan

    1. Yes this is a feature when using them backwards, personally I find the blank view preferable to another filter changing to another value without me being aware.

    2. Hi Ivan, I have exactly same issue here for the default filter value. Whenever I change level 1 or 2 filter, it ends up a blank. Any solution for version 9.1?

  3. This may have been stated, but I don’t think I picked it up. Is it possible for me to filter based on an a single “item number” which has 5 different categories assigned to it, but also be able to see all (or if possible the top N) other items that have those same 5 categories.

  4. I have one question that there should be own custom radio button with Eg:in a superstore example basically when we do quick filters on Region & State those relevant criteria values will display on different ways like radio button, sliders,e.t.c, but my requirement is there is a custom popup radio button box like quick filter with Region & State when we select any of that Region or State in the custom Popup Box, next level hierarchy should appear,how to do this

    thanks for your support….

  5. While we cascade quick filters, no value is selected by default so that does not lead to a great user experience. Is there a way to set some default?
    For Eg. I’ve Geography Level that has Global, Region and Country – I select all values in database. Next, I’ve Geography that displays only relevant values for corresponding Geography Level. But whenever I change Geography Level, all charts disappear because by default no value is selected under Geography filter.
    Any one who has a solution to this?

    1. Hi Jayanti,

      in regard to “The 5th Element”, I’m just giving you tips about your concern because I met the same situation and found the workaround. If you a dashboard instead of a worksheet, it will work properly.

      Cheers

      1. I am not sure about this only working on the dashboard and not on the worksheet. Generally, when we add any worksheet to the dashboard, the quick filters appearing on the dashboard does not seem to retain the same settings applied to them on the worksheet.

  6. Hi Team,

    A question for you – what can you do to change the order of filters for a filter action? How does that impact on the above options?

    Regards
    Chale

  7. Very nice article. For one more level of cascading (the 7th), just thing of applying again the same strategy used in level 6, but this time inside the Context! I worked for me.

  8. Just one simple question: I have a dashboard for survey data. There were about 40 questions in the survey. One set of questions for the overall organization, and the rest were divided among several business units. For example, you had a question about satisfaction in the overall organization, and one about your satisfaction with a particular business group.
    I set up a filter for business group, and then set up a questions filter using only relevant data from the business groups filter. It does a good job of that; when I select a particular business group in the groups filter, I only see questions pertaining to that group in the questions filter.
    However, say I select “sales,” from the groups filter, and “overall satisfaction” from the questions filter. If I then want to look at, say, “responsiveness” from the “IT” group, I can select “IT” from the groups filter, and I get only IT-related questions in the questions filter, but the original overall satisfaction question I checked earlier remains checked, so now the view is contaminated, showing results from an aggregate of those two questions.
    I hope this description makes sense. Does anyone have a fix for this? I can, of course, uncheck that satisfaction question before going to the next question, but that seems like it should be unnecessary.
    I’m pretty much a “newbie,” so if I’ve missed some really obvious fix, don’t hesitate to tell me. Thanks!

  9. I enjoyed and used the article. Any updates based on the newest version of Tableau now that this article is 3.5 years old?

Leave a Reply

Your email address will not be published. Required fields are marked *