Dynamic Hierarchy Filters: Making Use of Tableau 10.2’s Cross Join Calcs

When working with a client recently, I came across a challenge with multiple dimension filters that were cluttering up the dashboard. The filters could be grouped together as several hierarchies, but Tableau does not currently have a way to use hierarchies as their own quick filter. However, using cross joins, I found a simple work around.

Cross joins are not themselves a new feature in Tableau. In earlier versions, there are several ways to create cross joins, either by reshaping the data or by using custom SQL (see Alexander Mou’s post, 4 Approaches to Cross Join in Tableau). However, in Tableau 10.2, there is a new feature that allows us to join tables based on a calculation. This blog will demonstrate how to use this feature to create your own dynamic hierarchy filters.

The first thing that you want to do is create an Excel sheet with one column for your hierarchy choice, and another for the associated dimension choice. You also want to add another column with “1”, this will be used to create a cross join with the Sample Superstore data.

Open a new workbook in Tableau and connect to the main data source (in my case, Sample Superstore). In the Data Source tab, add a connection by clicking “add” next to the Connections header, then bring in the Excel sheet you created earlier. Next, you want to create an inner join between the two tables using the new 10.2 join calculation feature:

Write a calculation that will give each row the value “1”:

And then join to the “ID” field in the hierarchy/dimension choice table:

The logic behind this is that by giving each row a value of 1 and joining to the ID field in our Excel sheet that also has a value of 1, you will create an “all to all” relationship; in other words, every row of Sample Superstore will be joined to every row of your Excel sheet. Rows will be duplicated so that each row in Sample Superstore has a “Hierarchy Choice” and “Dimension Choice” from the table we created. You can see this by looking at the number of records. In the initial Sample Superstore data, we have 9,994 total records. After the join, we end up with 99,940 total records (the initial 9,994 records multiplied by the 10 rows created in our hierarchy/dimension choice table).

Since we have more records after joining tables, won’t our measures be duplicated and inflated?

It is logical to think that because our join duplicated the number of records, we may end up with inflated metrics. However, because of the quick filters, this is not actually the case.

For example, in the view above we can see that customer Darren Powers is repeated 10 times in the data. However, only the row that corresponds to the selected hierarchy “Customer” and the selected dimension “Customer Name” will be included in the view. Your figures will therefore remain at the appropriate level of detail and will not be duplicated.

Next, create a calculation to dynamically split your view into different dimensions based on your hierarchy and dimension selections:

Add this calculation to rows and build the view with your Hierarchy Choice and Dimension Choice fields as quick filters:

Make sure to set Dimension Choice to only show relevant values

Notice that by default, your quick filters will be sorted alphabetically:

To change this, right click on the Dimension Choice field in the dimension pane, click Default Properties, then Sort, and rearrange the values manually to create a sort based on your chosen hierarchy:

Bring everything together in a dashboard and you now have a view that allows users to select a hierarchy and a corresponding dimension to view sales figures for their area of interest: