The Tableau technique shown here is to use an action to update an entire dashboard, including the filter selection and the view which triggered the action. The trick doesn’t have to be used to navigate hierarchies or hierarchical data, but it happens to work very nicely for that purpose.
Let’s say you have a Tableau dashboard like this:
There’s a nice drop down that lets you select an individual and then see their Superior and any Underlings (or in the business world you might refer to them as “supervisors” and “direct reports” – but that’s far less exciting). The question that might be raised is, can I click on one of the superior or underlings and have it act as if I’d selected that person in the drop down?
I can hear you now:
In fact, here it is:
(But you won’t fail and you will believe it, because now, I’ll show you how!)
So, this data source is relatively straightforward. At its simplest, it is just a record per individual with an ID and a self-reference to point to the individual who is the direct superior.
In order to create views and actions that list superiors and underlings, I’m going to do a couple of self joins, like this:
Both are left joins so I have the full list of individuals but I also have a row for every superior for that individual (join on Reports To to ID) and a row for every underling for that individual (join on ID to Reports To). This is going to give me quite a few extra rows of data, and I’ll have to be careful if I’m using any measures to use LoD or some other technique to make sure I’m not double (or more) counting in aggregations. But for the purpose of building out a hierarchy, it works well.
The result is a nice list of fields in Dimensions, which I’ve cleaned up a bit:
I put these together as a dashboard, and at first it’s going to show everyone in every view. We’ll filter in a bit…
You could start with either the Superior or Underling view. It doesn’t matter. I’ll start with the Superior and create an action like this:
Notice several important things:
Getting an Action to update a filter control is actually very easy. Because… the action is a filter! So, I won’t even create a filter from a field and show it like I normally do. Instead, I’ll create the filter with an action and then show it as a control.
Once I’ve triggered one of the actions I just created and look at the view, I’ll see the action filter show up on the Filters shelf:
Notice that I’ve made the filter a global filter. That’s important because it means the action will impact everything (including the sheet that triggered it). You could opt to just apply it to some selected sheets.
And now, I can show the filter as a control on the dashboard. Using the drop down menu on one of the views (it’s a global filter, so any view will do), I simply find the Action filter and add it:
At this point, the end user can use the drop down or the actions and everything is fully operational!
And with this technique, you can do so much! Much more than just navigate hierarchies. You can do almost anything!
This Tableau technique is now the ultimate power in the universe. I suggest we use it!