Tableau Workaround Part 2: Exclude Values from Filter

This is the second in a multi-part series on Tableau. In case you missed it, here is part one.

Sometimes when using databases as data sources in Tableau, we need to “hide” certain values or metrics from one user group while providing visibility to the full data set to another user group.  Perhaps user groups are interested in a different subset of values or aggregations; we need to remove or hide others in specific situations. For example, think of a data set with sales results for a dozen product categories, but only four are relevant to your team. Tableau has an easy way to implement filters excluding these non-relevant values, but what if we also want a filter to allow users to cycle through all values?

Filters can either include or exclude values, and simply applying an include filter will allow any value present in the data to be selected. Creating an exclude filter would solve the problem, but unfortunately we can’t create an exclude filter and an include filter on the same dimension in Tableau.

This could be remedied by creating a separate data connection where you can manipulate the data using custom SQL or simply delete the undesirable fields.  However, duplicating data connections can lead to a whole new list of issues, especially if you want to filter across those connections. In our example below, we’ll show you a simple way to apply exclusions and inclusions on the same field in a view using the same data connection.

For this example, we used the Performance worksheet from the Superstore Tableau workbook. The visual illustrates sales by category. Our goal is to create a quick filter that only displays values we think are relevant while hiding other values. Below we’ll include ‘Furniture’ and ‘Office Supplies’ while hiding ‘Technology’.

Step 1 – Create an Include Filter on a Report

Creating an include or exclude filter is standard Tableau functionality. In this step, we will create an include filter. On the report where you want to add a filter, simply drag the field into the filters card. The filter can be multi-select or single select.

Under filters, right-click your field and select ‘Show quick filter’. On the right, you should see the filter appear, but the problem is that every field is still available to the user for viewing.

Step 2 – Create an Exclude Filter for the Same Column

We want to add an exclude filter on the same field, but we already have an include filter created on this field. The simplest approach is to duplicate the field. Right-click the column and duplicate the column within the dimensions.

Now drag the copy into the filters card, this time selecting it as an exclude filter and choosing the values you want to exclude from the report.

When you hit ‘OK’ you will see the quick filter update removing the option from the user to select the excluded value. You may need to select the ‘only relevant values’ option.

Now when a user interacts with the report they will not be allowed to select any value you prohibit from the quick filter. Below you can see the modified Performance worksheet from the Superstore Tableau workbook.

If you have questions about this blog post, please leave a comment below, tweet us @CrederaIT, or contact us online. Remember to stay tuned for the next installment in our blog series, Tableau Workaround Part 3: Total on Stacked Bar Chart.

• Business Intelligence
• SQL
• Tableau
• Include Filter
• Exclude Filter