Check out the workaround described here on Tableau Public: Tableau Filtering – Keep Null Out of List | Tableau Public
TL;DR – With this workaround in Tableau, you can always include a category in a viz while not including it as on option in a filter, making it always visible regardless of what the end user selects.
I recently had a client request along these lines that had me scratching my brain: “How can you configure a filter in Tableau to always display a certain category (for example, Null) without listing it as an option for users to exclude?” Depending on your dashboard audience, the word “null” can be ambiguous and have no clear meaning. If there were a way to exclude it from the filtering list while ensuring that null records were still displayed, we could keep end users happy while retaining the data in our view.
To recreate this scenario, I found a dataset containing all Dunkin Donuts locations in the US. For this example, I related this data to a census dataset showing population by county throughout the United States. Minor data preparation was performed in Dataiku.
In this hypothetical scenario, the end users of this data would like to see a map of all Dunkin’ Donuts locations and filter on whether or not the store also contains a Baskin Robbins. This should be displayed on top of all US counties colored by population. At first glance, it’s a simple request. We can construct a map using separate layers for counties and for stores, and then filter on whether or not the location has a Baskin Robbins:
Notice that our filter has an option for “Null.” Because of the structure of our data, the two datasets are essentially being full outer joined. As a result, the records in the final dataset for counties that do not contain a Dunkin’ Donuts store will have a “Null” record for whether or not the store contains a Baskin Robbins, since there is no store in the first place. If we exclude Null records, we can see this on the map:
We’ve lost all data for counties that don’t have a Dunkin’ Donuts. The easiest solution would be to tell our users to not exclude null records, but that could be a hard concept to teach depending on the size and technical aptitude of the dashboard audience. The better solution is to always include null records and not display it as an option to filter. Here’s how we can accomplish that.
- Create a calculation that recodes null records to have a value that already exists in the data. In this case, I am recoding null records to be true:
*NOTE: You may be asking why we couldn’t stop at this. After all, we no longer have null records in the data. However, if end users now filter out TRUE records, then you will lose those records that were originally null, when we want to keep them regardless of how the filter is configured.
- Create a set based on your new list calculation:
- Create a filter calculation:
Use this calculation as a filter set to TRUE on your view. From here, use the set created in step 2 as your filter:
We can now safely filter on the status of whether or not a store has a Baskin Robbins location and not worry about losing all other data.
Now, you probably noticed that we are now missing data for some, but not all counties. The workaround demonstrated here is not a perfect solution and has drawbacks. In this case, we now lose county information for counties that do contain stores as opposed to the other way around. Your mileage with this workaround may vary. If you want us to take a look at your dashboards and provide advice or other workarounds, feel free to reach out and see what we can do for you.