How to Drill into a Single Row in Tableau

Improve hierarchical drilldowns with this 3-step trick

Hierarchical drilldowns in Tableau are all or nothing, but in this video, Ryan shares a simple way to get around this default behavior. Learn how to drill into one or more dimension members in a text table or visualization.

Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you how a drill into a single dimension member in Tableau, whether the dimension that you are wanting to drill into is used on a text table or a visualization. First, let me show you why this is needed.

If I had the Category and Sub-Category dimensions on a text table- so let’s just throw out a use case here. Let’s say that I wanted to click on the Furniture dimension member and see the underlying sub-categories. Well, notice, this is all or nothing. Because I’ve got this Sub-Category dimension on the view, that’s the level of detail for this view.

One option is to put the Category and Sub-Category dimensions into a hierarchy. So I’m going to create a hierarchy. I’ll just leave the name as is. Click OK. That added this negative sign to the Category pill on the Rows Shelf.

I can now drill back up and drilldown as much as I want by clicking this plus and the minus sign. And if I go to Presentation mode, you could actually do this even when you’re on Tableau Public, Tableau Server, Tableau Online, by hovering near the top of the chart and clicking the plus or minus sign.

But the point of this is, this is all or nothing, which is not the use case that I described. I’d prefer to just be able to click on one of the three categories and see the respective underlying sub-categories. That’s what I’m going to show you how to do.

This involves set actions. So the first thing we need to do is to create a set that we will later click on. So the set, in this case, is based on the Category dimension. So I’m going to right-click on the Category dimension, hover over Create, and click Set. I will call this my Category Set.

And we are going to overwrite the values that are included in this set later, so for now I’m just going to leave this blank. And I’m going to click OK. We have a new set called Category Set. There’s one more calculation needed to get this to work. So I’m going to go ahead and create a calculated field. I’m going to give this a name, call this Category Selected.

And the most elegant way that I can think to write this formula is to use the Immediate IF function. So this starts with I-I-F. If you’re not familiar with that function, anytime you see something blue in a calculated field, you can click on it.

And open this little fly-out menu. It will look up what you clicked on. So this gives you the syntax on how to write the formula, and a little bit of a description of what it’s going to do. This says, “checks whether a condition is met and returns one value if that is true and another value if it is false.”

Well, sets are Boolean, so that means that a set by itself is a condition. It’s either true or false. So to start the first condition, I’m just going to type the name of the set, so “Category Set.” When that Category Set is true– so in other words, when the Category dimension members are in the set– I want it to display the Sub-category dimension.

If you look at the syntax over here on the right, it’s saying, when that condition before the first comma is true, show this value. Profit is the example they use. In our example, we’re saying, if the Category dimension is in the Category Set, we want to show the sub-category.

The next thing we need to do is type another comma. And what comes after the second comma is what you want displayed when the first condition is false. So those would be our categories that are not in the Category Set. When that is the case, for us, we just want to do an empty space. So we don’t want to show anything, and then close parenthesis, and that is the entire formula. I’m going to click OK.

That newly created Category Selected calculation that we just made is going to be the second drilldown. So we’re no longer going to be using that Category to Sub-category hierarchy. Instead, we’re going to add the Category Selected dimension after the Category dimension.

And notice, by default, all of the second column is blank. That is because, if you look at our set, it was empty when we created it. If I were to select one of these, like Office Supplies, and click Apply, that one gets expanded now, because it is in the set.

So you can kind of see the root of how this is going to work. Only when the Category dimension member is in this set will it show the Sub-category. I’m temporarily going to remove everything from the set again.

So you can already see it kind of starting to work. It’s nice, especially for you as an author, because you have the ability to change the values of that set. But now we’re going to make this really powerful and translate that– or transfer that control of the drilldown to our audience. And we’re going to do this via set dashboard actions. If this is new to you, I encourage you to watch the video An Introduction to Tableau Dashboard Actions.

But dashboard actions work on dashboards, which is one of the reasons they’re called that. I’m going to add the one sheet that we’ve created to the dashboard. And we don’t really need to pretty this up too much. But I’ll at least hide this title. And I’m going to click Dashboard in the top navigation and Actions. Click Add Action.

The type of dashboard action that we are looking for is called Change Set Values. This is a fairly intuitive interface. I’m going to call this Drilldown Example. And we’re just saying, when you click on this table, How to Drill into a Single Row, we want you to target the set in this area. This is the most important aspect of the set dashboard action interface.

By default, we have nothing selected. We’ve got the correct data source, but we need to use this dropdown to choose which set we are overwriting. We are overwriting the Category Set, so I’ll click on that. You also can choose what to do when you clear the selection.

In this case, I don’t love the default. It says “add all values to the set,” meaning if I click the Escape key to clear the selection, it would then show the sub-categories for every single category. That’s not quite what I want. I would probably go with “remove all values from set,” which will return this table to its default state where nothing is drilled into. But that is all the coding required for this particular dashboard action. I’m going to click OK. Click OK again.

We don’t see anything change, but now, if I were to click on a specific category, it drills into the sub-categories just for that one dimension member. Not the default behavior. This is a really elegant user experience, particularly if you’ve got a table with lots of rows in it.

It’s almost impossible to drilldown and show the dimension members in that second layer of the hierarchy for all of the first column. So this is a really nice way to just be more precise with your drilldowns. And it works regardless of which Category I click on.

Because this is a set action, you also have the ability to do a multi-select. So if I hold down the Control key while I click on a second category it will drill into a second category. But notice, the first one is still not drilled into. So that’s a nice additional bonus of set actions as compared to parameter actions, which only allow you to drill into one thing at a time.

One more note on this user experience. First of all, let me show you what happens when I clear the selection. Because we coded this to say, “just go back as if nothing happened,” when I click Escape, it just goes back to its default state.

But I do want to point out one more thing about this, which is, it works on tables particularly well, but it also works on charts. So if I were to add Sales– just added the measure of Sales to create a quick bar chart– this will still work. It’s just that we now have a visualization that we’re drilling into one row on.

This has been Ryan with Playfair Data TV – thanks