Filter Yourself: Using Reflexive Sheet Actions for Drilldowns or Disappearing Acts

Tableau’s dashboard actions allow easy interactivity between sheets. New users to Tableau are familiar with clicking the “Use as Filter” icon and seeing that everything they click just magically…works. More experienced users have likely gone into the Dashboard Actions menu and created their own Filter actions. Dashboard actions can easily filter across different fields, reference different datasources, or even take the user to a different dashboard. With Set Actions, Tableau has even enabled interactivity that didn’t previously exist. Set Actions make it easy for a sheet to even filter itself, adding and removing members to a set which is used as a filter.

The one thing that IS difficult in Tableau, however, is having sheets target themselves as filters. When creating a new dashboard action, you can set a sheet to target itself by setting it as both the Source and Target sheet, but it won’t change the behavior.

Even though the Map is both Source and Target, clicking a State doesn’t filter itself.

For the most part, we don’t want actions to target the source sheet. If the above action did select a single State, the top sheet would become useless (and create a confusing UI).

There are times, however, when this type of filtering makes sense. I’ve got an org chart dashboard which shows a selected employee, their manager, and everyone who works for them.

A reasonable follow-up question to this is “Who else works for Josh?”. I’d like to click on Josh McDaniels and filter the entire dashboard to show his manager, his employees, and his information. This requires having my Manager sheet filter itself when Josh is clicked on.

We’ve already shown, however, that selecting a sheet as Source and Target does nothing. Instead, the correct setup requires setting it as the source sheet, but changing the way we target it. Rather than selecting the Target as a sheet which is part of the dashboard, we target it as an individual sheet. We also need to target a different field…we’re clicking on a manager, but we want to treat him as an employee once the dashboard updates.

The target isn’t the “sheet as part of the dashboard”. Instead, choose the sheet as a standalone piece.

It’s important to set “Clearing the selection will:” to “Leave the Filter”. When a sheet filters itself, it first filters everything out. Because the sheet now returns an empty set on which nothing can be selected, Tableau (frustratingly) treats this as clearing the selection. We need the filter to persist through this phase of filtering.

So the combination of targeting a specific sheet and leaving the filter allows us to self-target with a dashboard action. This is immensely useful in any hierarchy, where we can easily click up or down through the hierarchy without ever having to reset the dashboard. Where else are self-actions useful? Hiding and showing a menu is another great use case.

Using the same org-chart dashboard example, we have to start by selecting an employee. Our end-user can log in, select a employee, and have the dashboard populate for that employee. From there, they can navigate to any point in the hierarchy by simply drilling up and down…but if they want to navigate to an employee in a totally different part of the company, they may have to go all the way to the top and then guess their way through upper management. Instead, we could provide them an interface to select an employee. Once they make a selection, the menu disappears. Again, we can accomplish this with a self-targeted action.

First, though, we’ll need two calculations. I call them [Dummy Dimension] and [Dummy Dimension 2]. [Dummy Dimension] is simply the integer 0, and [Dummy Dimension 2] is simply the integer 1. Now I set up a self-filter using the above methodology. To make the sheet hide itself, though, we have to set up a filter condition which returns an empty set.

Clicking on this sheet will now create an action which finds rows where [Dummy Dimension] = [Dummy Dimension 2]. As far as I’ve ever found, 1<>0, so the whole sheet is hidden.

Pairing this with other actions allows this click to hide the “menu” sheet as well as driving interactivity on the rest of the dashboard. Of course, you may need to get your menu back at some point. When you click on this action, it grabs the value 0 from [Dummy Dimension], but it only sets the filter on the field [Dummy Dimension 2]. The resulting filter is the equivalent of filtering [Dummy Dimension 2] for the value 1. Any filter which filters on [Dummy Dimension 2] by filtering for 1 instead will therefore replace this filter…re-showing the sheet.

Overall, you can use dashboard actions to create all kinds of magic filtering on a single dashboard. Whether it’s a sheet hiding itself, bringing that sheet back, or using actions to create in-place drill-up and drill-down through hierarchies, Dashboard Actions are far more flexible than the one-click option of “Use As Filter”. The dashboard I linked at the beginning of this post uses 12 different dashboard actions…across only 5 sheets. Can you do it in fewer?