Can you create a drill down using set actions?

For week 30, #WorkoutWednesday alumni Emma Whyte returned re-posting this challenge which was originally set in Week 41 of 2017 (see here). The idea behind this was to see how the challenge could be achieved using features that have been released since that challenge – in this case set actions.

I’ve been doing the #WorkoutWednesday challenges since they were first introduced, so I completed the original challenge, which is posted here.

Despite it being over 2 1/2 years ago, I had a strong recollection as to what was required to achieve this. So the challenge I set myself, was to recreate without looking at my own solution.

Building out the data

This is one of those challenges where we can build the data out into a table to check the functionality before building the actual viz. I always like to do this where possible, as I find it a good reference to make sure I’m getting the logic & the calculated fields I need right.

Start by adding State & City to Rows and add Sales & Profit via Measure Names on Columns .

As the challenge is to use Set Actions, then naturally, we’re going to need a Set. The Set we need is based on State with the idea being that when there is a State(s) in the set, then the City will display instead.

Selected State

Right click on State and Create -> Set. Select an option in the dialog, eg Alabama say

We will need to show the marks based on State or City depending on whether a State has been selected or not. We need a single field that we will use in the viz that displays the dimension we need to show

Display Value

IF [Selected State] THEN [City] ELSE [State] END

Add this onto the Rows and you’ll see how this is working

We can test the functionality of putting values into and out of the set without the need for the dashboard action at this point, by right-clicking on Selected State and selecting Show Set – the list of set values to select will display (a bit like a filter list).

We need a way to figure out what rows to show – how to identify whether there’s anything selected in the set.

Count States Selected

{FIXED : COUNTD(IF [Selected State] THEN [State] END)}

By being an LOD, this will set the count of the items in the set across all the rows in the data. Add to the sheet so you can see how this works

So we want to show information when either there isn’t anything in the set, or for the rows associated to the Selected States only

Records to Show

[Count States Selected] = 0 OR [Selected State]

Add this to Rows and test out… with no State in the set, all the rows are True

but with a State selected, only the rows associated to that State are True

But we seem to have too many marks showing when there’s nothing in the set….?

That’s fine.. just take City out of the view now, and if you deselect all States you should get the 48 rows we’re going to start with listed, and all are Records to Show = True. The Sales & Profit values will also now be aggregated to the appropriate level.

Building the Viz

Ensure your Selected States set is empty, and build out the scatter plot

  • Profit on Columns
  • Sales on Rows
  • Display Value on Detail & Label
  • Records to Show on Filter set to True
  • Mark Type = Shape set to x

Verify the functionality by clicking a State in the list, and the view should change to show the City.

We need to colour the marks based on Profit

-ve Profit?


Add this to the Colour shelf and adjust colours accordingly.

Finally we need to look at how the title/subtitle changes based on which level we’re at.


IF [Count States Selected] = 0 THEN ‘Sales vs. Profit by State’
ELSE ‘Sales vs. Profit for ‘ + [State]


IF [Count States Selected] = 0 THEN ‘Select a state to drill down to city level’
ELSE ‘Double-click a city to drill up to state level’

Add these onto the Detail shelf, then they’ll be available to reference in the Title of the sheet.

And then adjust the Tooltip, and we’re pretty much ready to go.

Adding the Set Action

Create a dashboard and add the scatter plot sheet to it.

Add a dashboard action to Change Set Values which runs on the Select action, and assigns values to the Selected State. On clearing the selection, values are removed from the set.

And that should pretty much be it. My published version is here. I thoroughly enjoyed the ‘throwback’ to previous challenges, and would like to see this theme continue on occasion.

Happy vizzin’! Stay Safe


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s