Filtering in Tableau While Keeping a Specific Category

Data

Filtering in Tableau While Keeping a Specific Category

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.

The Workaround

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.

Population data prep shown 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:

Map data in Tableau showing Dunkin Donuts location data in US

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:

Map data in Tableau showing Dunkin Donuts location data in US minus counties without a DD

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.

  1. 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:Recording null records as true in Dataiku

*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.

  1. Create a set based on your new list calculation: Editing HBR List Set
  2. Create a filter calculation: Creating a filter calculation for HBR Filter

Use this calculation as a filter set to TRUE on your view. From here, use the set created in step 2 as your filter:Map filtering DD locations and US population, creating list of counties without a DD

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.

More About the Author

Aaron Carmichael

Analytics Consultant
How to Configure a Local dbt Core Environment Setting Up a Personal dbt Instance Over the past few weeks, I have been diving into dbt in my free time to learn more about the tool as ...
Filtering in Tableau While Keeping a Specific Category Check out the workaround described here on Tableau Public: Tableau Filtering – Keep Null Out of List | Tableau Public TL;DR – ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!