How to Drill into a Single Row of a Text Table in Tableau

For this example, I want to display the Sub-Category dimension (i.e. the second level of the hierarchy) when a dimension member from the Category dimension is in the set. The most elegant way I can think to write this formula is:

IIF([Category Set],[Sub-Category], “”)

The Immediate IF function looks at the logic before the first comma; when the statement is true, it displays the outcome between the first and second comma; when the statement is false, it displays the outcome after the second comma. Since sets are Boolean, the Category Set from step one alone is a logical statement; it’s essentially a more elegant way to say “IF a Category dimension member matches the dimension member in the set THEN show the Sub-Category dimension ELSE show a blank END”.

I will now place this newly created calculated field onto the Rows Shelf of my table.

Nothing changed because our Category Set is currently empty, and the Sub-Category dimension will only be displayed if a dimension member from the Category dimension matches a dimension member in the set we created in the first step.

Populate the drill down set using dashboard actions in Tableau

In the third and final step, we will use a set dashboard action to populate the Category Set, which will create the single-row drilldown effect. To create a dashboard action, the sheet has to be on a dashboard, so I’ve thrown the Text Table sheet containing my Category and Sub-Category Drilldown dimensions on a new dashboard.

To create a set action, (1) click Dashboard in the top navigation, (2) click “Actions…” from the list of options, and (3) choose “Change Set Values…”.

The most important aspect of the Change Set Values dashboard action settings are to tell Tableau which set you are targeting, but you can also set up when you want the action to execute and what you want to happen when the selection is cleared.

For this use case, I recommend having the action run on “Select” and “Removing all values from set” when the selection is cleared. This means the single row drilldown will work when a user clicks on a dimension member and the table will go back as if nothing happened when the selection is cleared.

An Introduction to Tableau Dashboard Actions

Now if a user clicks on a specific dimension member, such as the Office Supplies category, the table will drill into the sub-categories for that single row.

When they clear the selection by clicking somewhere else on the view or the Escape key, the table returns to its default state with no sub-category detail.

In the case that you want to drill into individual rows at the same time, this approach also works with a multi-select by holding the Control key while you click on multiple dimension members. By the way, this works any time the Category, or whatever dimension you are analyzing, is on the view – so it will also work on visualizations.

For more ways to get the most out of text in Tableau, read the related post, 10 Tableau Text Tips in Ten Minutes.

Thanks for reading,
– Ryan