<< Back

How to action filter when using set/parameter action drill-downs in Tableau


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’:

Select sub-category calculation

Create a set from ‘Selected sub-category’ and a calculated field called ‘Selected manufacturer’:

Selected manufacturer calculation

Add these fields to the view along with a measure:

Drill-down sheet

Create a second sheet with Segment and a measure in the view:

Segment sheet

Add these both to a dashboard:

Drill-down and action filtering dashboard

Step 2 – configure the set action drill-downs

Configure the set action for the Category set:

Category set action

And the set action for the Selected sub-category set:

Selected sub-category set action

Make sure the drill down is working however:

Set action drill-down gif

Adding an action filter from the drill-down sheet to the Segment sheet results in:

Broken action filtering gif

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:

Drill-down level calculation

Add this to the view on rows and return to the dashboard to see how using the drill-down affects the numbers:

Drill-down level gif

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:

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.

Filter to max drill-down level configuration

At this point, I navigate to the dashboard and check that the calculation is working correctly, before adding to filters:

Drill-down filter check

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:

Selected manufacturer filter

We don’t need the Drill-down level on the Drill-down sheet, so you can remove this or simply hide the header.

Drill-down with action filtering gif

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!


Originally posted on https://warumdatum.wordpress.com/

Andrew Lehm

London, UK

2 thoughts on “How to action filter when using set/parameter action drill-downs in Tableau

Leave a Reply

Your email address will not be published. Required fields are marked *