September 11, 2023

How to Build a Hierarchy Filter in Tableau: Part 1 of 3

By Venkatesh lyer

The filters you add up in a dashboard will often be different hierarchy levels. For example, Region and State or Category and Sub-Category. When filters are added like this, you will lose some much-needed context regarding which sub-categories roll up to which category.

This is not a new problem in Tableau. In fact, this was floated as an idea in the Tableau community almost ten years ago. It is a very practical and intuitive way of filtering, but it takes work to implement in a BI tool. 

Over the years, many people have taken a shot at this and have come up with excellent solutions. Check out the examples put out by Tableau Visionaries Rosario Gauna and Marc Reid from the previous link. Using these in actual business dashboards is often impractical because it relies on the performance-draining duo of parameters and string functions. 

In this blog, you will learn how to build a hierarchy filter in Tableau that is as fast as regular filters and can be scaled up to work in actual business dashboards. It will be visually similar to the hierarchy filter showcased by Rosario Gauna in her blog and thus will share a few of the initial calculations in common.

A gif showing the fully functional hierarchy filter in action.


You can follow along with the Orders table of the latest (2020-2023) Superstore dataset. To avoid issues down the line, it is best to use data densification. 

To do that, you must create a table with the same number of whole numbers as the number of levels in your hierarchy. 

To follow along with this blog, you just need three, corresponding to the three levels: All Sub-Categories, Category, and Sub-Category. But add four so that it is easier to use the same workbook to build hierarchies with four levels that will be covered in part 2 of this blog.

Open a notepad and paste the following in it.


Save it somewhere, and then in the Data Source pane, add this file as a text file connection and drag it in relation to the Orders table. Then, create a relationship between the two using a relationship calculation of 1=1.

A gif showing the process of data densification needed for the hierarchy filter

Step 1: Build the Hierarchy

The Levels field has an extra value than what is needed here. So, create the following calculation.

					//Sub-Category Level

Convert this to a dimension.

Then, using that, create the following calculation.

					//Sub-Category Hierarchy
CASE [Sub-Category Level]
WHEN 0 THEN "All Sub-Categories"
WHEN 1 THEN "All Sub-Categories" + " >> " + [Category]
WHEN 2 THEN "All Sub-Categories" + " >> " + [Category] + " >> " + [Sub-Category]

Drag this in the Rows Shelf, and you will get the following.

A screenshot showing the [Sub-Category Hierarchy] when added in the Rows shelf

After this, create the following calculation.

					//Sub-Category Header
CASE [Sub-Category Level]
WHEN 0 THEN "All Sub-Categories"
WHEN 1 THEN [Category]
WHEN 2 THEN [Sub-Category]

Next, create a Set using all values from the [Sub-Category] field.

This set will act as the backbone of the entire hierarchy filter. All the sub-categories under a node (or the sub-category itself) clicked in the hierarchy will be added to this set. 

Create the following calculation to inform the user which sub-categories/nodes are selected.

					//Sub-Category Shape
IF NOT { FIXED [Sub-Category Hierarchy]: MAX([Sub-Category Set])} THEN "❑"
ELSEIF { FIXED [Sub-Category Hierarchy]: MIN([Sub-Category Set])} THEN "▣"
ELSE "◩"


Whenever a set is added inside a calculation, it acts as a boolean calculation, returning True against the rows corresponding to the values in the set and False against the ones that are not. Fixing this at the [Sub-Category Hierarchy] will perform this check for every row in the view. This is an advanced version of the technique explained in this blog.

{ FIXED [Sub-Category Hierarchy]: MAX([Sub-Category Set])} from the “IF” part of the calculation will return True if the Set is not empty for that aggregation level. Adding a NOT before that will return True only when the no values from that node (or that node itself in case of sub-categories) are added in the set. Hence, the empty symbol.

{ FIXED [Sub-Category Hierarchy]: MIN([Sub-Category Set])} from the “ELSEIF” part will return True only if all values from that node (or that node itself in case of Sub-Categories) are added in the set. Hence, the filled symbol.

Nodes that do not fall in either of the two categories, the “ELSE” part, will get the partially filled symbol.

Now, you have everything you need to build the label for the hierarchy with the following calculation.

					//Sub-Category Label
SPACE([Sub-Category Level]*3)
[Sub-Category Shape]
" "
[Sub-Category Header]

The Space function indents each level of the hierarchy by adding three times the number of spaces as the level.

Drag this next to the right of the [Sub-Category Hierarchy] pill in the Rows Shelf.

A screenshot showing the result of adding the [Sub-Category Label] calculation in the Rows shelf

Untick Show Header for the [Sub-Category Hierarchy] pill. Right-click on the [Sub-Category Set] and tick on Show Set. This will show a card that resembles the filter card.

Play around with this and watch how the shapes change for the values of the [Sub-Category Label] field.

A screenshot showing how the shapes change when values are selected/deselected from the set

In addition, another feature you will find in most hierarchy filters is one that indicates the number of child nodes selected against each parent node. To do that here, create the following calculation.

					//Sub-Category Selection
    [Sub-Category Level] !=2 
    "(" +
    STR({ FIXED [Sub-Category Hierarchy]: COUNTD(IF [Sub-Category Set] THEN [Sub-Category] END)})
    + "/" +
    STR({ FIXED [Sub-Category Hierarchy]: COUNTD([Sub-Category])})
    + ")"

This calculation will return a fraction showing the distinct count of sub-categories added in the set for a node over the distinct count of sub-categories under that node.

Add this in the Rows shelf to the far right.

A screenshot showing the [Sub-Category Selection] calculation in action

It works exactly as expected! Remove the [Sub-Category Selection] pill from the Rows shelf and modify the [Sub-Category Label] calculation as shown below.

					//Sub-Category Label
SPACE([Sub-Category Level]*3)
[Sub-Category Shape]
" "
[Sub-Category Header]
" "
[Sub-Category Selection]


Step 2: Format the View

To make the hierarchy look much better, you will have to placeholders. To do that, double-click on the Columns shelf and type 1 in the blank pill that opens up. Right-click and convert this to a dimension.

Next, drag the [Sub-Category Label] pill from the Rows shelf to the Label section of the Marks card. Change the Marks type to Bar and add the [Sub-Category Level] in the Colors section.

Now, edit the axis from 0 to 1 and untick Show Header. Change the font (with hierarchies, it is best to use mono fonts), font size, color, color border, etc., to your liking. Set the bar size to maximum and align the label towards the left.

A screenshot showing the end result of formatting the placeholder

You can add the Sales for each node to provide more context to the users. To do that, Ctrl+drag the 1 field to the right of itself to duplicate it. Then add the Sales on top of the [Sub-Category Label] pill in the 1(2) Marks card to replace it.

Again, edit the axis to go from 0 to 1. Tick on Dual Axis and Synchronize Axis. Add Percent of Totals. Tweak the formatting as needed, and you will have something like this.

When using placeholders, it is often a good idea to alias them so that it is easier to track. Double-click on the first 1 pill and type //Label before the 1.

A screenshot showing the process of adding aliases to the placeholders

Hit Shift+Enter and then Enter.

A screenshot showing the result of adding an alias to a placeholder field

Similarly, alias the other 1 pill as Value.

Step 3: Set up the Actions

As everything is driven by the [Sub-Category Set], you must use Set Actions. Since one sheet can have either the “Add values to set” or “Remove values from set” option, you will need two sheets, one with buttons for adding values to the set and the other to remove values from the set.

To start with, create the following calculation.

					//Sub-Category Add
IIF([Sub-Category Shape]="▣","","✚")

This calculation will only add a “✚” symbol for the [Sub-Category Hierarchy] nodes, which are not entirely added to the set.

Drag [Sub-Category Hierarchy] and [Sub-Category Add] to the Rows shelf on a new Sheet. Set the fit to Entire View. Also, right-click on [Sub-Category Set] and tick Show Set.

A screenshot showing the [Sub-Category Add] calculation in the Rows shelf

The objective is to add all the sub-categories that roll up to a node (or the node itself) to get added in the [Sub-Category Set] when the corresponding “✚” is clicked on. The way the hierarchy is built is conducive to this. To see why, drag [Sub-Category] in the Rows shelf.

A screenshot showing the sub-categories against each row of the hierarchy

When a “✚” is clicked, every sub-category next to it must get added to the set. Add a set action like the one shown below.

A screenshot showing the set action that governs the addition of values in the set

It works as expected! Try it out! Note that you will have to deselect using the set that was shown.

The [Sub-Category] pill in the Rows shelf could be more user-friendly. To fix this, drag it into the Marks card’s Detail section. Change the Marks type to Polygon.

On a side note, since the [Sub-Category Add] is added in the Rows shelf, setting the Marks type to Polygon was unnecessary. Bar/Area also would have sufficed. However, if you use the placeholder technique to create this sheet, you must make a dual axis of two placeholders, one with the “✚” and another with the Marks type set to Polygon with [Sub-Category] pill in Detail.

Hide the [Sub-Category Hierarchy] pill, Left align the [Sub-Category Add] field and format the sheet. You will get something like this.

A screenshot showing the fully formatted sheet for adding values in the set

Now, duplicate this sheet to create the sheet that will remove the sub-categories when clicked. To do that, replace the [Sub-Category Add] calculation with the following.

					//Sub-Category Remove
IIF([Sub-Category Shape]="❑","","✖")
A screenshot showing the fully formatted sheet for removing values from the set

Also, edit the duplicate set action that was created as follows.

A screenshot showing the set action governing the removal values from the set

Play around with Sheet 2 and Sheet (2) to try adding/removing sub-categories from the set.

Step 4: Add in a Dashboard

Rename the sheets Sheet 1, Sheet 2, and Sheet 2 (2) as Sub-Category Hierarchy, Sub-Category Add, and Sub-Category Remove, respectively. Hide the Show Set Card and other Legend Cards on the three sheets.

Create a new dashboard, add a horizontal container, and then a blank object. Drag the Add, Remove, and Hierarchy sheets, in that order from left to right, inside this horizontal container.

Change the width of the Add and Remove sheets to 40 pixels each, hide titles, remove unnecessary padding, etc.

Then, update the two Set Actions to run from the Dashboard, as shown below.

A screenshot showing the process of changing the set action to run from the dashboard instead of the worksheet

Once you finish that, you will have a fully functional hierarchy filter!

A screenshot showing the final result

This particular variant of the hierarchy filter has many advantages:

  • Adding the number of sub-categories that roll up to the node, sales, and percent of totals for sales provides some vital additional context for the users.

  • Add the [Sub-Category Set] to apply the filter in any Filters shelf. The filtering action will be as fast as a regular dimension filter.

  • Since the set keeps track of everything and the calculations determining the shapes run directly on it, it scales extremely well with data.

  • New categories and sub-categories will directly get added to the hierarchy filter without any manual intervention.

  • For some additional flexibility, the set can be added inside calculations while filtering. This will be explained in more detail in Part 3.

  • Having separate add and remove selection buttons makes completely removing/adding a partially selected node a one-click action. It is a bit of a stretch, but I love it. 

That being said, there is one drawback with the way this hierarchy filter works.

Since there are three sheets side by side and all of them must be lined up, you cannot use a hierarchy that is more than a page long unless you do some form of synchronized scrolling or have some sections of your hierarchy collapse (a variant of this will be explained in part 2,) so that the three sheets are lined up. 

However, the scales often start tipping in favor of having a few disparate filters over a hierarchy filter when you have a long and/or a very ragged hierarchy anyway. So, pick the scenarios where you will employ this, bearing this limitation in mind and with adequate foresight.


It is worth mentioning that the major drawback of this method was the inability to configure two types of set actions in one sheet. Tableau Visionary Rosario Gauna added an idea to the Tableau forum a few months ago that will resolve this. It is a minor update, but it will be a game-changer!! Support this idea if you have made it so far. The more, the merrier.

Hopefully, you enjoyed learning this rather intricate technique of building a hierarchy filter. It is fast and intuitive, scales well with data, and doesn’t look bad. Use it in your dashboards to give your users a unique filtering experience.

Stay tuned for Part 2 and Part 3!

If you want more information on the Hierarchy Filter in Tableau,
contact our team of experts!

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit