How to Clean Your Filter

For my first post of actual content, I thought I'd start off with something that is quite simple but also is an extremely effective way to solve a problem that many Tableau developers face very regularly; that problem being the issue of restricting entries that appear in a Quick Filter list.

Essentially what this tip addresses is the need to allow the your users to make a selection from a list of filter values but also for you as the author to be able exclude certain records from that filter list. You may wish to do this because selecting (or even viewing) one of the extra values... may look untidy, may cause confusion, may result in an empty worksheet, or even cause a security issue. So, after we've created our basic filter, what options do we have to be able to solve this problem? Go to the Source!

This filter needs cleaning!

One method is to set a Data Source Filter which you can do so in six simple steps:

1. right-click the name of the data source
2. click Edit Data Source Filters
3. choose the field on which you will base your filters
4. select the values you wish to exclude
5. click the Exclude checkbox
6. click OK twice

How to create a Data Source Filter that excludes values from a dimension

Any Quick Filter you have based on the field at step 3 will then refresh to no longer show the excluded values. In this particular example, a couple more clicks are required to prepare the Quick Filter: open the context menu for the Quick Filter by clicking the down arrow in the top-right of the Quick Filter panel click Include Values Now your users will be able to choose which values they wish to keep rather than choosing values to exclude from the view.

Switch your Quick Filter from Exclude to Include

Using a Data Source Filter to restrict Quick Filter entries is very simple to do but is quite brutal because excluding values at this level means that none of the rows that contain these values will be available to any worksheet that connects to your data source.

For example, let’s say there was a separate view in my workbook that showed inventory levels for different products in my retail store.  Since we excluded the NULL Customer Name in the data source filter, we would not be able to see any new product lines.  This is because no customers have bought them yet therefore would have NULL in the Customer Name field after joins.

Copy That, Good Buddy

The following is the most commonly described solution you’ll find around the Web and for good reason – it works!  Although, it requires the source field be duplicated and there are a few more steps involved:

1. duplicate your dimension
2. rename the new dimension
3. right-click your original dimension pill
4. uncheck Show Quick Filter
5. right-click your new dimension pill
6. click Show Quick Filter
7. bring up the context menu for the Quick Filter
8. click Only relevant values

After choosing the ‘Only relevant values’ option those values previously excluded in your original dimension filter will disappear.

How to restrict your Quick Filter entries with a cascading filter

Sets Appeal

I find this option to be a little more elegant, simpler and fit for purpose.  Sets already have a close relationship with filters and this requires no duplication of fields.

1. right-click your dimension pill on the filters shelf
2. click Create Set
3. give your set a name
4. click OK
5. drag the newly created set and drop it directly on top of the dimension pill on the filters shelf
6. right-click the set pill
7. click Show Quick Filter

How to use Tableau sets to restrict your Quick Filter entries

There you have it.  The extraneous values have gone and your Quick Filter is now a neat and clean list.

One drawback of these methods is that you can't apply the same techniques to a filter that is based on Tableau's generated fields such as Longitude, Latitude or Measure Names.  Measure Values can't be used either, but if you tried to create a dimension filter using Measure Values, Tableau will in stead create one using Measure Names.

If you have any questions or any thoughts on how to better solve this problem, please feel free to add a comment below.

TFB