Let’s talk about Sets, baby…
Working with a customer recently, the requirement was to show a list of excluded cities on one worksheet from a list of states and cities on another worksheet. My initial thought was sets, then they through a curve ball and wanted the filter controls to only show relevant values. Now, I know this works in traditional filters, but with Sets it is a little different.
Then, it dawned on my… you can add Sets to hierarchies, and you now have the ability to Show Values in Hierarchy when showing the filter controls. So, could I do a hierarchy of sets, and will the set controls have the same Values in Hierarchy option?
YES! YES YOU CAN!
Using Superstore, I created a Set on State and a Set on City, then created their own hierarchy … both sets are Excluding values.
Adding them to filters on the worksheet, and showing the Set Controls yielded me this view… so far so good.
On the City Set control, I checked for the option and BAM, there it was.
Now, I created a second sheet and a new calculation to give me the inverse of the sets, to show we what is excluded.
Applying this to the second sheet, with State and City on the Rows, placing both on a dashboard, and I’d cracked the code… or at least I thought I did… duplicate cities did me in and the state wasn’t showing up the way I wanted, so I ended up creating a concatenated field of city and state, created a set on that field (because you can’t create a set on a “Combined Field”, and was able to use the same Not [Set] type calc to show the items excluded.
I share this in the event that your use case doesn’t have duplicates. Using the hierarchy filter feature is a great option for limiting set values where there is a relationship and dependency. Never be afraid to experiment, you may just surprise yourself!