Set Actions: Drilling

Get to the Data that's Most Important

Posted by David Velleca on April 27, 2019

[Set Actions] + [Clever Calculated Fields] = [Awesome Drilling]

A couple weeks ago, I tweeted a Tableau Confession:

Over the past few months, I've been exploring what is really possible with Set Actions. I've used them to filter, drive highlighting across multiple sheets, highlight in Viz in Tooltips (which I think is AWESOME), and even at work to provide asymmetric drill downs in crosstabs (when I have to use them...). As with most skills or concepts, through use, my proficiency has increased and I've moved on to trying new things.

For #ThrowbackDataThursday this past week, the dataset dealt with the history of the NFL Draft. In my viz, I used a variation on asymmetric drilling to provide the user the opportunity for a more in depth look at the draft. The default view includes a bar chart showing the number of draft picks by year for each position. When the user clicks on a year, the viz drills in to show the data for each round in the selected year - excluding all other years. The viz is at the bottom of this post, but you can play with the concept here:

Ok, so let's walk through the how I did this. Feel free to head over to the ThrowbackDataThursday website to download the data if you want to play along at home... Ok, so the first step to using Set Actions is creating your set. I created my set on [Year] and used the incredibly imaginative name [Year Set]. To start with, I had no members in the set.

Without any additional work here, the set action would only add or remove members of the set. To take advantage of the set action, we need to leverage some clever calculated fields. As a reminder, I wanted to drill from [Year] to [Round]. The first calculated field is really simple - we'll use an IF statement to return the [Round] if the [Year] is a member of [Year Set]:

Responsive image

I put this calculated field - [Year - Round Drill] on the Columns shelf. Now, if we wanted to use typical asymmetric drilling, we'd be good with just this calculation, but that would not result in the outcome I was looking for - I wanted to isolate the viz to only the selected year. I knew that this would require a filter on the view that responded to the changes of the set members. At a high level, the logic is really straight forward - if there are no members in the set, then show all years. If there are members in the set, then only show me the year that is in the set.

Understanding that you can use a set in your calculated field to evaluate to True/False, I was able to leverage that along with level of detail calculations to allow me to look across the entire view. Let's look at the calc and then I'll explain:

Responsive image

The first part of the calc looks across the entire set and if there are NO members in the set, it returns a 1. The second part of the IF statement looks at the year level and checks if a particular year is in the set. If the year is in the set, it also brings back a 1. If the year is NOT in the set, it also fails that first condition (that the set is empty), and gives a 0. I then put this calc - [Year Set Filter] - on the Filter shelf, set to 1.

To test that this all works, the only piece left is to add a measure to rows and add the set action. Knowing that I have one record per Draft Pick in my dataset, I simply used SUM([Number of Records]). Now let's setup our set action.

Responsive image

We want to drive the action on a click, so we'll choose 'Run action on: Select.' We'll choose our Dataset (defaults to the correct DS if there is only one), and choose our [Year Set]. Then we have to choose what to do when the action is cleared - when the user clicks again. I want to drill back up to all years, so I know that I need to remove all members of the set so my Filter calculation will evaluate to 1. Click OK and test it out!

So with most concepts in Tableau, we can take it a few steps further. For instance, I wanted to drive my BANS to reflect the right values for the set member, so I applied my filter across both sheets. I also wanted to have responsive tooltips and labels that would indicate the chosen year - a couple more calculated fields, some formatting and I was there.

Check out my finished product below. This isn't the flashiest viz from a visual perspective, but really happy with the interactivity. How could you use this method in your vizzes? Let me know on Twitter! I'd love to hear how you could use this concept.