“I have a lot of data and want my users to be able filter the dashboard. However, there are a few options in the filter that I don’t want them to be able to select. How can I remove these from the dropdown but not filter them out when ‘All’ has been selected?”

This Tableau solution came about from the above client question, but there could be a few use cases when you might want to omit your filters from a dropdown like this. Perhaps you want to include a region in the data, but for security reasons, you don’t want users to be able to filter to that region specifically, when viewing HR data, for example. Maybe you want to see overall information about all the groups, but if a group has less than five employees, you don’t want the user to be able to filter that in order to protect anonymity. Another example could be that when looking at sales data, filtering to a specific region does not give a lot of helpful information, so it’s unnecessary for users to have that option.

When it comes to implementing a solution, there are a few options for this!

The first is to create a parameter and use that to filter the data. This is a great option. However, if the data changes (i.e. you get a new region in your data) you have to edit the parameter to include that. Also, parameters (as of 2021.3) do not allow for multi-select. So, we need another way to go about doing this.

The second way is to create an extra sheet for the dashboard we’re going to use to remove those options. It can then be added as either a title or hidden within the dashboard.

Create a Copy of the Field to Remove Options From

In our example, that is the Region field. I want my users to be able to either see the data from all regions or select South, East and/or Central. I do not want them to be able to filter down to the West region:

To do this, duplicate the field. You can leave the default name (‘Region (copy)’), but I prefer to rename and add to remove options from filter, it so I remember what the field is intended to do.

Add Original Field to Filters Card

Bring the original field (in our example, Region) to the Filter card on your sheet and select All:

Add Duplicated Field to Filters Card

Bring the duplicated field (Region – to remove options from filter) to the Filter card. Here is where you’re now going to change the filter type to Exclude and select the options you do not want your user to be able to select.

Important note: Excluding options, rather than selecting a static list, will give you the flexibility of new options automatically being added to the filter. If you want to keep a static list, regardless of new data, you should select only the options you want. For this example, we want to remove the option for the user to be able to select the West region:

Show Original Field Filter

Right-click on the original field in the Filters card and select Show Filter. This will allow us to select whether we want to see it as a drop-down, single-select, multi-select, etc.:

Show Only Relevant Values for That Filter

Now is when we start to see the exclusion come into play. Click on the drop-down arrow in the top-right of your filter, and select Only Relevant Values. This will remove the West region option because of the Region – to remove options from filter filter that we added:

Add Title to the Text in This Sheet

In this example, I’m going to use this sheet I’ve created to be the title on my dashboard. I do that by adding an ad hoc field to the Text box in the Marks card and then editing the Text:

Add Sheet to Your Dashboard

To now utilize this new filter, you need to bring this sheet to your dashboard. I already have a chart in my dashboard showing my Sales and Profits broken up by region. I’m going to put the newly created title to be above the chart. I’m also going to do a few formatting things, such as hiding the title of the sheet, make it fit the entire view and shorten it a bit:

Doing this brings in that new region filter that does not have West as an option.

Apply Filter to All Sheets in Dashboard

As of now, the region filter that was brought in only applies to that Title sheet. I would like it to apply to all sheets in the dashboard. To do this, click on filter and then the down arrow in the right-hand corner and select Apply to Worksheets > All Using This Data Source. You can also select specific worksheets it applies to if you don’t want it to apply to all:

You’re Done!

That’s it. You can play around with it and see that when you select (All), you will see the data from the West region, but you are not able to filter to it specifically:

Download the attached workbook if you want to dive into it further.

Note: There is a small bug in Tableau where if you deselect All then choose all three options individually, Tableau will check the All button, even though it’s only showing those three regions. If you clear it then click All, it will show up.