Don’t Get Caught in the Relevant Value Filter Trap
Alright, you've built a dashboard and then the client asks for 8 filters to be added. You add them as requested. The client then asks if the filters could update based on other filter selections, i.e. you choose the United States from the Country filter and the State/Province filter would only show US States - it would not show Canadian Provinces. So you go through and set all the filters to show Only Relevant Values. You give it a test run and find that the relevant filters keep trapping you in this endless web of confusion. You make one selection, it trims the values in all the other filters, but when you want to change it, you find yourself trying to dig yourself out of the relevant values maze you've created for yourself.
I'm sure we've all had this experience. It's quite frustrating. And you can imagine that if it's this difficult for us (the ones actually doing the development work in Tableau Desktop), you can imagine what that experience might be like for an end user.
The problem comes due to the fact that every filter is relevant to every other filter. Let's take an example where we are using four filters:
Now let's really spell it out:
Country depends on Region
Country depends on State/Province
Country depends on City
Region depends on Country
Region depends on State/Province
Region depends on City
State/Province depends on Country
State/Province depends on Region
State/Province depends on City
City depends on Country
City depends on Region
City depends on State/Province
That is 12 different dependencies when using just four filters. If you had eight filters, there would be 56 different dependencies! It's not hard to see how a user can get tangled up in this mess.
But, as you probably guessed since I'm writing this blog post, there is a solution!!! I call it a "Relevant Value Filter Hierarchy". It does take some setup and isn't perfect for every use case, but it can be incredibly useful in many situations. It will work by allowing us to dictate what each filter is dependent upon. I personally think the simplest way for users to understand it is for each filter to be dependent upon all filters above it. So let's use the same list of filters we used in the example above:
So in this example, Country will act independently of all other filters. Then Region will depend on Country only. State/Province will depend on both Country and Region. And City will depend on Country, Region, and State/Province. At the risk of repeating myself, each filter will be dependent upon all filters above them in the filter hierarchy.
Okay, let's actually build this. For this workthrough, we will be using a version of my Superstore KPI dashboard. You can access this version on Tableau Public. Note that I've build out all the calculations in this workbook already, so you can just follow along or delete the calculations and start from scratch.
To get this to work, we won't actually be using standard filters. We will utilize sets then use those sets for filtering. So as a starting point, let's create a set for the first filter in our hierarchy, Country. Go ahead and include all values and to make the hierarchy a bit easier for us to understand, let's number the first one with a preceding 1, i.e. "1) Country".
Okay, now let's move onto the next filter in the hierarchy, Region. We want this to be dependent upon the selections in the one filter above it...1) Country. To do this, create the following calculation:
2) Region Calculation // Need this to show relevant values based on the Country set IF [Country] IN [1) Country Set] THEN [Region] ELSE '*Not in Criteria*' END
Before we explore this calculation, let's look at the relationship between Country and Region. You'll see that the US contains all 4 regions, but Canada only includes 3 (it does not have a South region).
When we actually go to set this up, we will place the Country Set onto the filter and use a set control (we will discuss this more later) to allow users to pick a country. Let's assume they choose Canada. The above calculation - 2) Region Calculation - will determine which countries are in the country set (just Canada) then yield Regions contained within those countries: Central, East, and West. For the regions that don't meet this criteria - the South region - this calculation will replace it's value with '*Not in Criteria*'. (The asterisks will just move this value to the top of the list).
In plain language, it says, "give me the regions that are associated with the selected countries".
We've set this up with just Country and Region...but the two other fields downstream will rely on region, so let's take the calculation we just created, and create a set from it.
Although we've only set this up with Country and Region, let's go ahead and add it to our dashboard so that we can show how this works.
Drop both the 1) Country Set and the 2) Region Calculation Set onto the filter shelf. Right-click on each of these and choose the option "Show In/Out of Set" and when the filter window pops up, check the box next to In. Next, right-click on each and choose "Show Set". When you do this, it will provide you with a "set control". It looks like a filter, but it's actually just a mechanism to add/remove values to/from a set. (My workbook may have the filters and sets formatted with white text, so you'll need to modify that to something darker in order to see them). Check out the gif below:
Okay, now let's see how they interact with one another. Go ahead and uncheck the United States leaving only Canada. In the Region set, you should now only see the 3 regions that correspond with Canada and a fourth "not in criteria" option. The Region set is now completely dependent upon the selections in the Country set - pretty cool, huh?
Okay, let's create a calculation for the State/Province. In this one, we want it to depend on both the Country and the Region. Our logic will be the same as the previous calculation, but we will have to have two sets of criteria:
3) State/Province Calculation // Need this to show relevant values based on the Country set and the Region set IF [Country] IN [1) Country Set] AND [Region] IN [2) Region Calculation Set] THEN [State/Province] ELSE '*Not in Criteria*' END
So in this one, it requires the country to be in the country set and it requires the region to be in the region calculation set. When it meets the criteria, we yield the State/Province field (and when it doesn't, we yield the 'not in criteria' result).
Now let's do the same thing we did above:
- Create a set off of this calculation
- Add the set to the filter shelf
- Right-click and choose the "Show In/Out of Set" and when the filter window pops up, check the box next to In
- Right-click on each and choose "Show Set"
Now give it a test run. See how it continues to filter the results down relative to the two filters above it?
From here, we just continue to repeat the steps above by creating calculations with the required criteria, creating sets, and setting them up on the canvas. As reference, if we wanted to add in City, the City Calculation would look like the following:
4) City Calculation // Need this to show relevant values based on the Country set and the Region set and the State/Province set IF [Country] IN [1) Country Set] AND [Region] IN [2) Region Calculation Set] AND [State/Province] IN [3) State/Province Calculation Set] THEN [City] ELSE '*Not in Criteria*' END
Then create a set off of this and apply the steps above. This will then depend on the 3 selections above.
The linked workbook has applied this technique using all four fields, applied the filters to all sheets in the workbook, then showed them on the dashboard itself. Check it out.
Although I think it works best to do this in a hierarchy, you could use this in any way you want by making any "filter" dependent upon any other filter OF YOUR CHOOSING. You can control exactly how it works, simply by adding the criteria to your calculation, then creating sets from those calculations. It's completely flexible!
I hope to see this used out in the wild. Let me know if you have any questions or thoughts. Thanks for reading!
Important Note: when I released this blog post, our good friend and regular contributor, Kirk Munroe, messaged me and said you can do this to some degree with a standard hierarchy in Tableau. For example, with Superstore, Location is a hierarchy containing Country, Region, State/Province and City.
If you add these four filters then show them, then edit them, you'll see an option for "All Values in Hierarchy".
When this is selected, then each filter will only be relative to the fields above it in the hierarchy. So State/Province would be relative to Country and Region. 🤯 Mind blown!!!!!!!! I had no idea you could do this! I would imagine that this would actually cover most uses cases, but if you need full control, you can use my set method above. Thanks, Kirk, for ruining yet another blog post of mine.😉
Very cool technique! Thanks for sharing
ReplyDeleteWhat is the advantage/disadvantage of this technique over using the built-in 'All Values in Hierarchy' filter setting?
ReplyDeleteHonestly, I had no idea you could do this until Kirk Munroe told me this morning. I have added it to the blog post. I think this would handle most needs. My technique may be useful when you want full control and you can dictate what it is relative to. Thanks for the note!
DeleteOne hack you could apply to get that unwanted option to the bottom of the list:
ReplyDeleteIF [Country] IN [1) Country Set] THEN [Region]
ELSE CHAR(65520)+'*Not in Criteria*'
END
Good call on that, Ray!
DeleteI got that sort character from Gemini - not sure exactly why that value works, so readers should be forewarned.
Delete