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

Leave a Reply

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