<< Back

Removing values from quick filters using Tableau’s Order of Operations

Sometimes you want to hide certain values from filters on your dashboards. There are a few tricks you can use to make this happen, all you need is a little knowledge of Tableau’s Order of Operations.

This is the Order of Operations that Tableau uses when performs calculations, going from top to bottom.

To remove options from filter menus you need to add an extra layer to your filtering, using an earlier stage of the order of operations. Here are a few tricks to make this happen, and why they work.

First things first, you’ll need to make sure your filter isn’t set to show “All Values in Database”, otherwise any earlier filtering won’t apply and Tableau will just display everything in the data set.

To make any earlier filtering work, we need to use an option other than All Values in Database when editing the filter from the drop down menu.

Option 1: Duplicate Field

This is quite a simple method – make a duplicate of whatever field you are using to filter, then use this duplicate to filter, deselecting or excluding whichever values you don’t want to appear.

You can create a copy of any field by right clicking on it in the data pane and selecting duplicate.

By filtering by both the original and the duplicate field, the filters will be calculated at the same time, but the original filter will only show values which pass through the duplicate as it is only showing relevant values.

A safer option is to add the duplicate field to context, then set the original filter to only show values in context. This means that the duplicate field will be calculated first, then the values from that will pass down to our original filter.

Here I’ve added by duplicate Sub-Category field to context which is shown by its grey colour on the filter shelf. I’ve sent set the original Sub-Category field to only show values in context.

The choice between the two will depend on any other filters you may have in your sheet or dashboard and whether you want the duplicate filter to apply higher or lower in the order of operations.

Option 2: Using a Set Filter

Secondly, you could use a set to filter out unwanted values. All you need to do is create a new set from your original field, selecting whichever values you want to remain or to exclude, then place this set on your filter shelf. Then just make sure it is set to Show Members in Set.

You can create a new set from a dimension by right clicking on it, going to create, then selecting set.
Once you place the set on the filters shelf, right click it and make sure it is set to Show Members in Set

As sets are calculated before dimension filters, this will filter out anything not in the set before the filter is shown.

Combining the two

The useful thing here is that set filters can also be added to context, so if you already have dimension filters in context, you can still modify the values these display by creating a set and adding that to context – the order of operations for these are “nested”:

This can give you a lot of freedom in filtering values in a certain order. When you also consider this can also apply to fixed level of detail calculations or top N filters, you have may options for how to slice & dice your data to display exactly what you want, especially if you already have a complex set of filters applied to your sheet.

Here’s an example of using three layers – a context set, a context dimension filter then the original dimension quick filter which would be displayed on the dashboard.

The first set only brings through Sub-Categories starting with A or B:

I’ve added the set to context, with only the first five sub-categories selected.

The duplicate field in context then takes those values and only allows sub-categories starting the A to pass through.

The duplicate Sub-Category field is added to context, but the options to select are already limited by the values passing through the context set filter.

This means our final filter only allows users to select values beginning with A.

The only values by end user will be able to see in the Sub-Category filter are the ones which pass through both context filters.

This is a very simple example, so may not fully show how useful this can be since I could just filter down to all the As at one level.

However, this is a clear example of how using and understanding the order of operations in Tableau can take your dashboard building to the next level.

Nick Jastrzebski

London, UK

3 thoughts on “Removing values from quick filters using Tableau’s Order of Operations

  1. Hi Nick – this was a great example. Have you ever need to create a filter for the user showing a range of Year values (say 1999 to 2020) and you want to have a default number of years selected (say Max(Year) i.e. 2020 and 3 years less. So when the user interacts with the filter, it always shows max year >= Max Year – 3 Years. Essentially, current year and the past three are always check off.

    1. Hi Kris, glad you liked the article. To your question, there are lots of options for limiting the choices in filters but not so much when it comes to pre-selecting values as you mention. You might be best off looking into relative date filters as these can be set to show the the last X number of years for instance, with the option to include the filter control for you users to change the number of years. It’s important to note that they aren’t dimension filters though, so they’ll come further down the order of operations.

      1. Hi Nick, sounds like I am on the same page. I actually tried the ‘relative dates filter’ before I sent my message. I extended the though into using sets. One ‘set’ is taking care of the Relative Dates aspect ‘showing members in a set’.

        Additionally, I baked in a condition using a calc field where is fetches current year minus a variable number of years I want to ultimately display to the end user as selected. The condition is straight forward stating: Max(Year) >= MAX(Current -4). So this is the Relative Dates Set pill in the filters.

        Then, I duplicated this pill as ‘Relative Dates Set (copy)’…I know I was creative with the naming lol. I added a ‘Top’ property to this filter ‘By Field’ fetch the Top ‘Last N Years’ which is a parameter fetching the last 25 years worth of Years. Min set to 1 and Max set to 25, current value is set to 25.

        Last, I created a ‘Combined Set’ to leverage UNION choosing the ‘Shared members in both sets’ option. This is placed in the Filters box and exposed to the end user as a listing of years.

        Seemingly, this works with one minor glitch, the variable driving the Current -4 calc field does not seem to me working when I change the year number to test it from 4 to 8 or whichever number to test it out.

        I’ll spend a bit more on this as this issue as the key thing here is that variable and having it dynamic to the years that are transacted and populated to the warehouse year over year i.e. if current year is 2023, it should fetch the max year minus the variable 3 or 4 year etc.

        Thanks again for the suggestion and somewhat of a confirmation that there really is not a out-of-box answer for this.

        Side note: it would be awesome if Tableau gave dimensions the capability for the developer to choose ‘custom SQL’ to write behind the dim column. This would be done in 5 seconds (for the above requirement).

        Best,
        Kris

Leave a Reply

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