I really like set and parameter action drill-downs on a dashboard – when used well, they can help to add granularity to the data specific – user-selected – categories, where adding all of the data would overwhelm and dilute the original message.
However, I struggled for a while to incorporate them into dashboards where I was using action filters between sheets. Sometimes everything worked fine, but every now and then I would get some behaviour that would make the dashboard unusable – often clicking on a sheet to drill-down would completely blank out a sheet that it was supposed to filter.
I assume this is due to something going wrong in the order that the actions are being processed, but I couldn’t find a way to force them to occur in the right order.
If this sounds like something you’ve encountered and would like a solution to – you’re in luck! I found one.
What is the solution?
The solution is basically to create a drill-down level number that counts what level of the drill-down each data point is at, to include that on whichever sheet needs filtering, and filter that sheet to be the window_max(drill-down level).
How to set this up?
I will walk through the solution to this using the Sample Superstore data set included with Tableau. I will create a simple drill-down from Category to Sub-category to Manufacturer and this will filter a sheet based on Segment.
You can find the workbook I created these in here on my Tableau public.
I will be using set actions as the drill-down is easier to set up and to clear – which works for the purposes of this blog – but parameter actions are faster and have a lot more versatility around drilling back up – I can recommend this blog post by Rosario Gauna for setting up some very nice drill-ups!
Step 1 – creating the drill down
There are a few parts to this, I’m going to move through them fairly quickly as there are heaps of blogs outlining how to do this better than I can.
Create a set from Category and create a calculated field called ‘Selected sub-category’:
Create a set from ‘Selected sub-category’ and a calculated field called ‘Selected manufacturer’:
Add these fields to the view along with a measure:
Create a second sheet with Segment and a measure in the view:
Add these both to a dashboard:
Step 2 – configure the set action drill-downs
Configure the set action for the Category set:
And the set action for the Selected sub-category set:
Make sure the drill down is working however:
Adding an action filter from the drill-down sheet to the Segment sheet results in:
Notice how depending where the user clicks, different filtering behaviour is observed.
Let’s create a solution to get around this
Step 4 – Create a drill-down level number
Navigate to the drill-down sheet and create a new calculated field called ‘Drill-down level’. This field will count which level of the drill-down the user is at.
In the example, I have 3 levels:
1 – Category
2 – Sub-category
3 – Manufacturer
The following calculation returns me a value corresponding to the level I have drilled down to:
Add this to the view on rows and return to the dashboard to see how using the drill-down affects the numbers:
Notice how the level increases as granularity is added for the sections that drill-down, but the level stays at 1 for the un-selected Categories, and 2 for the un-selected Sub-categories.
For this action to filter the Segment sheet, we need to filter to the highest drill-down number currently in the view.
Step 3 – configure the filters
Navigate to the Segment sheet and add the Drill-down level field to rows.
Create a ‘Filter to max drill-down level’ calculation:
Add this to rows as well and make sure it is computed using Drill-down level only. This ensures that it filters to the max level across all data in the sheet.
At this point, I navigate to the dashboard and check that the calculation is working correctly, before adding to filters:
Step 4 – adding all of the filters
Add the Filter to max drill-down level calculation to the filters shelf and set to True.
We need to keep the Drill-down level in the view – this can be done via hiding the header, or just putting the field on detail on the marks card.
Navigate back to the dashboard and add a dashboard filter action for Selected manufacturer from the Drill-down sheet to the Segment sheet, as the filtering to a single manufacturer is not possibly via the Drill-down level number:
We don’t need the Drill-down level on the Drill-down sheet, so you can remove this or simply hide the header.
What next?
Now you have a technique for clean filtering between sheets while using a set action drill-down.
Now why not try to achieve the same effect using parameter actions and include some of the more user-friendly features possible with parameter actions.
Enjoy!
Datum 