Hierarchical Selections in Tableau

A question came up on the Tableau Community Forums recently asking if it was possible to create hierarchical selections in Tableau, where you can select an item at one level of a hierarchy and have all child nodes below that item become selected.

I searched around and couldn’t find any similar examples in Tableau and didn’t see an obvious solution so when I had some time I explored the idea further to see if I could replicate the functionality.

This is what I came up  with:

A Few Considerations

While the solution works with this configuration, there’s a few points to bear in mind:

• It requires the data to be unioned to itself once for each level of the hierarchy
• There are a number of nested table calculations with custom addressing, which may not work in all circumstances
• It uses parameters as a data source, which has data volume and performance limitations

There may be a better/simpler solution, but this was one of those experiments where I went down one path and just kept going until I got a working solution. That said, it would be interesting to see other approaches.

Given the calculations and methods used here, I wouldn’t recommend using this solution for business critical dashboards. It was a fun experiment and an opportunity for learning and, I hope, useful for personal projects where you may have a similar need.

What follows is an overview of the approach I took with explanations of the main calculations.

A simple data source with three levels is used:

Creating the Hierarchy

The first problem was how to create the three levels of the hierarchy. For this, I unioned the data to itself 3 times. I could then use the Tableau-generated “Table Name” field, which has three values, to construct the relevant value at each level.

Storing the Selections

Three parameters are used as data sources to store the hierarchy selections for each level. I’ve written about this technique here and here and, as always, I would highly recommend Jonathan Drummey’s blog post on this topic.

Below is a cropped view of the 3 parameters with various selections made:

You can see that the higher level values are repeated in the lower levels. This is required for other calculations, for example, when items are being added and removed to ensure that values from the correct branch are modified.

Check Box Icons

To create the check boxes, I used three unicode characters. When testing on Server, these codes didn’t render so well, so I added another three and gave the option to switch between them using a boolean parameter (p.Desktop), which can be toggled based on if you’re viewing in Desktop or Server:

Draw the Hierarchy

The calculation below renders each level of the hierarchy based on whether or not the item at that level of the hierarchy is contained within that level’s respective parameter or, for levels 1 and 2, in the parameter at the level below to show a partial selection, to represent that at least one value in this branch is selected, but not the whole branch.

There is also an Indent calculation which simply allows for the number of spaces in the indent to be adjusted dynamically with parameter.

Initially, this renders as below, with levels 1 and 2 repeating based on the number of level 3 values:

The below filter is added to remove the duplicates:

Which results in the following:

To ensure the correct ordering of the values in the hierarchy, other dimension are also in the view with their headers hidden:

Updating the Stored Selections

The trickiest part was generating the right values to send to each of the three parameters that store the current selections at each level. A different value for each parameter is generated based on which level is being selected or deselected.

Some of the logic I wanted to implement here includes:

• When selecting a final level 3 item, the level 2 item above it should also be selected
• If that results in the final level 2 item being selected for a branch, then the level 1 item should also be selected
• When selecting a level 1 or 2 item, all items in the same branch below that should be selected, but items in other branches should not be selected. For example, in the above hierarchy, NAM and EMEA branches should be totally independent even though they are using the same parameters to store their values
• When deselecting a level 1 or 2 item, all levels below in the same branch should be deselected. Again, while not affecting other branches.

The three parameter value calculations below follow a similar format. The value that is generated and passed to the respective parameter is dependent on:

• The level of the hierarchy being selected
• The level of the hierarchy being updated
• The current status of the item being selected

For example, in the hierarchy shown earlier, let’s say France (at Level 2) is not selected and I click on it. That means France becomes selected. Below is the impact on the 3 levels:

Level 1: If the UK is already selected, then EMEA needs to be made selected as all its child nodes are now selected. Otherwise EMEA remains unchanged
Level 2: France is toggled from not selected to selected
Level 3: All child nodes under France need to be selected regardless of their current state.

Level 1 Parameter Value

Below is the calculation for the Level 1 parameter value that is passed to the Level 1 parameter.

Clicking on the level 1 check box will add or remove the item based on the existing state. Clicking on a level 2 or 3 items requires the above logic checks to be made regarding whether this is the final item being selected at that level. Calculations for determining the number of selections at a level are shown later on.

Level 2 Parameter Value

Next is the calculation for the Level 2 parameter value that is passed to the Level 2 parameter.

The first two parts are similar to the prior calculation. The final part, where a level 1 item is selected, requires more work. In this case, all level 2 items under the same level 1 branch that was selected must be added to the parameter.

However, as some items may already be selected, and because I don’t want to store duplicates, which would impact other calculations, all existing level 2 values within this branch must first be removed. This is done using the REGEX_REPLACE function.

Then, a delimited list of all level 2 values in this branch is created using the PREVIOUS_VALUE calculation (which you don’t see below as it’s nested in a WINDOW_MAX calculation.

Below is the PREVIOUS_VALUE calculation referenced above.

You can read more about the PREVIOUS_VALUE calculation in my earlier blog post.

It needs to be nested in a separate calculation as PREVIOUS_VALUE can’t be directly nested inside a window calculation, however, it can be nested if it’s in a separate calculation.

Level 3 Parameter Value

Finally, the level 3 parameter value calculation. The same logic of building up a list of values is required, but this time twice, as there are now two levels above which both require a list of level 3 values to be generated.

How Many Items are Selected?

To determine if a level 2 or 3 item is the final one being selected in that branch, it’s necessary to know how many items there are at that level of the branch, which is found using a level of detail calculation:

The number of current selections is calculated as follows. The level 1 item is replaced with blanks in the level 2 parameter, which reduces the length by N times the length of the level 1 value. Dividing the difference by the length of the level 1 value returns the number of times it is in the parameter:

Explore the Workbook

You can interact with and download the workbook on Tableau Public here. The workbook includes some examples of how this technique could be used for filtering and highlighting in different charts, as shown in the video below:

I hope this was useful and thanks for reading.
Marc

• Twitter
• LinkedIn
• Link
• YouTube

Latest YouTube video
This video tutorial steps through how to create a dot plot in Tableau.