Focused Drill Down with Tableau

Tableau’s drill down feature hits a limit when you want to explore data with multiple dimensions with a high cardinality. I want to show you a way to make browsing tree structures in Tableau more comfortable.

Comparison of drill down mechanics in Tableau and Excel

Lets compare how you browse a hierarchical tree structure in Tableau and in Excel to clarifiy what we want to achieve. I am using an Excel file with random data in 100,000 rows and thirteen columns containing ten dimensions (strings) and three values (doubles). This is how the data is organized:

Hierarchical organised example data

What is a use case for that? I am working in Online Marketing and we organize campaign data in tree structures, where the top-most level represents the country, then comes the channel (SEO, display, …). Below that we have e.g. the publisher name and so on. The last level usually shows the ad format or keyword. Another use case is geographical data containing the country at the top level, going down to the city, district or street.

To browse my data I created a pivot table and drilled down into the latest level, “dimension10”. The drill down does not take longer than a couple of milliseconds, even for this large dataset. And that’s the result in Excel:

Drill down in a pivot table in Excel

Now lets do the same thing with Tableau. I assume you know how to create a hiearchy in Tableau, so I skip to the important step: The drill down. When browsing through the tree structure I faced three problems:

1. The entire drill down to the deepest level (“dimension10”) takes around 10 seconds (raw calculation time).
2. The sheets shows all 100.000 rows.
3. I dont see all columns.

Drill down in Tableau

Overview? Gone. But what went wrong? If you click the expand icon in Excel, the next level will be expanded for the selected value only. Or even more detailed: If you click on nameA (in column dimension2), you get all rows from column dimension3, but only if dimension2 == nameA. The interaction is implemented into the data itself. In Tableau you interact with the data structure. If you click the expand icon (or select “drill down” from the context menu) the complete view expands to the next level. In detail: You select the drill down for column dimension2 and Tableau returns all rows from column dimension3. Thats all.

Of course there is a workaround for that: Before you start the drill down, filter for the value (like nameA). But you have to do this for every single level and this will create a filter for every level you drill down.

Wouldn’t it be easier to just drill down like you do in Excel? Click a node and only see the child nodes that belong to this node. It would be faster and the result would not be packed with numbers from other nodes. Let me show you how to achieve that in Tableau with some filters, actions and parameters.

Brief overview

To help you understand how this solution works, I have this nice little visualisation of the process:

How the focussed drill down works

As you can see wee need a dashboard containing two worksheets. Every dimension will be represented by an index showing the level of the current dimension. We will concatenate the values of the dimension columns into a dot-seperated-path. We put that value into the field “current level concatenated”.

If you click a row in the “main” worksheet, the parameter “current level index” will change to the selected value of “next level index”. The parameter “level name concatenated” will be set to the selected value in “current level concatenated”. A filter will match for this value. The worksheet “go back” will do the same thing vice versa.

It’s important to understand the difference of “level name concatenated” and “current level filter”. Imagine you see all rows from level 1and you click the row containing “nameA” to drill down to level 2. Now you want to see every row from level 2 that fits the condition dimension1 == nameA (remember: dimension1 is the first level). That’s what the “current level filter” is for and that’s why it’s always the same value in the table. The “current level concatenated”, in contrast, holds the information about the current level of the particular row. That’s why it contains different values. It becoms the current level filter, as soon as you click on of the rows.

Sounds complicated? Let’s get to the details to help you understand.

Create two parameters

Press Shift+Cmd+P (Windows: Shift+Ctrl+P) or click somewhere in your data area and select “Create parameter”. Name this parameter “current level index”. Data type is integer, current value is 1 and the list of allowed values should contain as many numbers as you have dimensions, ten in our case. Create another parameter named “level name concatenated”, data type string, allow all values. If you’re done, right click the parameters and select “show parameter” to add them to your worksheet (this is for testing purposes only).

This parameter controls the current selected level

This parameter controls the filter

Create some fields

Click on the just created parameter “level name concatenated” and select “create” and “calculated field”. The name is “current level concatenated” and this is the required formula:

CASE [current level index] 
WHEN 1  THEN [Dimension1] 
WHEN 2  THEN [Dimension1] + "." + [Dimension2]
WHEN 3  THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3]
WHEN 4  THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4]
WHEN 5  THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5]
WHEN 6  THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6]
WHEN 7  THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6] + "." + [Dimension7]
WHEN 8  THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6] + "." + [Dimension7] + "." + [Dimension8]
WHEN 9  THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6] + "." + [Dimension7] + "." + [Dimension8] + "." + [Dimension9]
WHEN 10 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6] + "." + [Dimension7] + "." + [Dimension8] + "." + [Dimension9] + "." + [Dimension10]
END

Based on the current selected level index, this field concatenates the values of our ten dimension columns. (Make sure, that this field is registered as a dimension and appears in the field name area). To check if it works, drag the just created field to the row area and change the value of the “current level index”-parameter. You should see something this:

Based on the current level index, our first field concateates the levels into a dot-path-notation

We also need this kind of formula for the previous level. Create a calculated field named “previous level concatenated” and add this formula:

CASE [current level index] 
WHEN 1 THEN ""
WHEN 2 THEN "."
WHEN 3 THEN REGEXP_EXTRACT([level name concatenated], "(\w+)\.\w+") 
WHEN 4 THEN REGEXP_EXTRACT([level name concatenated], "(\w+\.\w+)\.\w+") 
WHEN 5 THEN REGEXP_EXTRACT([level name concatenated], "(\w+\.\w+\.\w+)\.\w+") 
WHEN 6 THEN REGEXP_EXTRACT([level name concatenated], "(\w+\.\w+\.\w+\.\w+)\.\w+") 
WHEN 7 THEN REGEXP_EXTRACT([level name concatenated], "(\w+\.\w+\.\w+\.\w+\.\w+)\.\w+")
WHEN 8 THEN REGEXP_EXTRACT([level name concatenated], "(\w+\.\w+\.\w+\.\w+\.\w+\.\w+)\.\w+")
WHEN 9 THEN REGEXP_EXTRACT([level name concatenated], "(\w+\.\w+\.\w+\.\w+\.\w+\.\w+\.\w+)\.\w+")
WHEN 10 THEN REGEXP_EXTRACT([level name concatenated], "(\w+\.\w+\.\w+\.\w+\.\w+\.\w+\.\w+\.\w+)\.\w+")
END

This is a bit messy, so let me explain. Imagine we are in the third level (nameA.nameB.nameC), then this part of the formula would be relevant:

REGEXP_EXTRACT([level name concatenated], "(\w+)\.\w+")

We are using a regular expression to get the part of the path, that points to the parent-parent of the current level. Why not the parent? The parent (nameA.nameB) is used to filter the current view.

But this field will be relevant when drilling up. That’s why we need nameA — the parent’s parent.

Now create another calculated field named “current level” and add this formula:

CASE [current level index]
WHEN 1 THEN [Dimension1]  
WHEN 2 THEN [Dimension2]
WHEN 3 THEN [Dimension3]
WHEN 4 THEN [Dimension4] 
WHEN 5 THEN [Dimension5] 
WHEN 6 THEN [Dimension6] 
WHEN 7 THEN [Dimension7] 
WHEN 8 THEN [Dimension8] 
WHEN 9 THEN [Dimension9] 
WHEN 10 THEN [Dimension10] 
END

This field returns the value of particular column (dimension) represented by the current selected level index. We need another field, similar to this, which enables us to actually filter the view: “current level filter”:

CASE [current level index]
WHEN 1 THEN "." 
WHEN 2 THEN [Dimension1] 
WHEN 3 THEN [Dimension1] + "." + [Dimension2]
WHEN 4 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3]
WHEN 5 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4]
WHEN 6 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5]
WHEN 7 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6]
WHEN 8 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6] + "." + [Dimension7]
WHEN 9 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6] + "." + [Dimension7] + "." + [Dimension8]
WHEN 10 THEN [Dimension1] + "." + [Dimension2] + "." + [Dimension3] + "." + [Dimension4] + "." + [Dimension5] + "." + [Dimension6] + "." + [Dimension7] + "." + [Dimension8] + "." + [Dimension9]
END

How does this help? Imagine we drilled down to the 4th level, e.g. “nameA.nameC.nameB.nameF”. The parameter “level name concatenated” points to the parent level: “current level filter”:”. Later we will create a filter, that only shows rows where “current level filter” (which we just created) matches this parameter. Simple, right?

Next you create two calculated fields “previous level index” and “next level index”. We need them to increase and decrease the level index. This is for the “previous level index”:

IF [current level index] = 1 then 1
ELSEIF [current level index] = 2 then 1
ELSEIF [current level index] = 3 then 2
ELSEIF [current level index] = 4 then 3
ELSEIF [current level index] = 5 then 4
ELSEIF [current level index] = 6 then 5
ELSEIF [current level index] = 7 then 6
ELSEIF [current level index] = 8 then 7
ELSEIF [current level index] = 9 then 8
ELSEIF [current level index] = 10 then 9
END

And “next level index”:

IF     [current level index] = 1 THEN 2
ELSEIF [current level index] = 2 THEN 3
ELSEIF [current level index] = 3 THEN 4
ELSEIF [current level index] = 4 THEN 5
ELSEIF [current level index] = 5 THEN 6
ELSEIF [current level index] = 6 THEN 7
ELSEIF [current level index] = 7 THEN 8
ELSEIF [current level index] = 8 THEN 9
ELSEIF [current level index] = 9 THEN 10
END

Side note: Those are the long versions, the smart formulas would be indeed:

[current level index] + 1

and

[current level index] - 1

You should now have five new fields, all registered as dimensions (example value):

• current level (nameA)
• previous level concatenated (nameA.nameB)
• current level concatenated (nameA.nameB.nameC)
• current level filter (nameA.nameB)
• next level index (4)
• previous level index (2)

Time to ...

Prepare the worksheets

You may now pull “next level index”, “current level” and “current level concatenated” to your main worksheet. It should look like this:

The fields “previous level index” and “previous level concat” belong to the worksheet “go back”:

Create the dashboard

Now we are going to connect everything. Let’s create some “actions”. Actions in Tableau are very powerful. Triggered by a click or mouse hover, you can move between dashboard and worksheets, change views or assign values to filters or parameters. We need to assign values to parameters:

Add a new dashboard, drag the two worksheets onto it and select the action-item from the dashboard-menu. You will now add four actions to change a parameter. Lets start with the “increase level index” action:

The action to increase the level index parameter

Make sure you only select the main worksheet as a source. The target parameter is the “current level index”. The field list defines, where the value for this parameter comes from: “next level index”, with no aggregation. Done. Vice versa the “decrease level index” action:

The action to decrease the level index parameter

No need to explain. I guess. Let’s move on with the action “set next level filter”. This takes the value from “level name concatenated” and put it to the parameter with the same name: “level name concatenated”:

The action to set the level parameter to the next level, e.g. nameA.nameB.nameC.nameD

Again vice versa, this time for the “set previous level filter” action.

The action to set the level parameter to the previous level, e.g. nameA.nameB

This action simply sets the value of the parameter “level name concatenated” to the value from the calculated field “previous level concatenated”.

You are almost there. You may now test the dashboard: As you can see, a click into the rows of the “main” worksheet triggers a drill down or drill up. But you still see all rows. Because your are missing one final setting:

The filter

Drag the calculated field “current level filter” into the filter area to use it as a filter. In the just opened dialogue go to “condition” tab and select “formula” to enter this line:

[level name concatenated] = [current level filter]

Now a click on a row of the main worksheet not only drills down to the next level, it also filters the view. Moving down to the deepest level, “dimension10" only take milliseconds and is not as messy as before:

A fast and perfect focussed drill down in Tableau

Visual improvements

There are some tweaks, not only from the technical side, to improve the visual presentation and make it look cleaner:

• Remove the parameter control elements on the right side.
• You can hide the index column and the column containing the concatenated string, just right click the values and uncheck “show headers”:

How to hide complete columns

• Double-click the title of the main worksheet and add <current level filter> to display the current path instead of the worksheet’s name.
• You can add an additional worksheet with a chart showing the data of the current select level.

The final dashboard

I bet there are a couple of more things to do. But the most important part: You can now easily drill up and drill down into your hierarchical organized data With almost no delay and you also will not loose the focus.

If you want to play with the result, you find it on Tableau Online.