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.
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.
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.
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.
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.
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:
The duplicate field in context then takes those values and only allows sub-categories starting the A to pass through.
This means our final filter only allows users to select values beginning with A.
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.