Better Way to “Catch” Multiple Slicer Selections in a Formula

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas,
IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

A Popular Topic

No, I don’t mean things like “Fox Urine” or “Face to Anogenitaled” – those are pretty funny of course, and they come up in my job because I consult for my scientist neighbor on his lab rat projects.

But no, I’m here to talk about something even more popular than Fox Urine

Every day, one of the most-read topics on this blog is some variant of “I want to catch slicer selections in formulas.”  This has been covered in at least three different posts:

1. https://p3adaptive.com/2010/06/use-slicer-values-in-a-calculation-with-powerpivot-dax/
2. https://p3adaptive.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/ 
3. https://p3adaptive.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

There have also been a number of comments on each post that suggested alternate (and often better) ways of doing things.  I’ve never been terribly comfortable with that third post in particular, the one dealing with multiple selections.

Funny thing is, I used a new technique (for me) in the Calendar Chart posts, but never went back and called it out explicitly.  Today I am going to correct that omission.

Catching in DAX vs. Catching in Excel:  This Post is About the Latter

Right up front, I want to make a distinction:  sometimes you want to catch a user’s slicer selections and use those in a measure.  Other times you want to catch them and use them in “normal” Excel formulas in a worksheet.

This post is about catching in Excel.  Kasper’s post above (#1) is a good example of catching in DAX, as is ANY post dealing with disconnected slicers.

This Technique Only Works With PowerPivot or Excel 2013!

If you’re not using PowerPivot yet, here’s yet another good reason to get started.  This problem is SO much easier to solve with PowerPivot (or Excel 2013) than “normal” Excel.  Go download it and come back

CUBESET!

First step is to write a single CUBESET() formula:

=CUBESET(“PowerPivot Data”, Slicer_Description3,”This is My Set”)

1) In PowerPivot v1 and v2, the first argument will always be “PowerPivot Data”.  In 2013, I think it may be “Data Model” but I’d have to double check to be sure.

2) The second argument is the “formula-approved name” for your slicer.  To see that, click on the slicer, and check its options dialog:

Finding the Name of the Slicer to Use in Your Formula

3) The third argument can be left blank, but then you get a blank cell in your sheet.  I set it to “This is My Set” so that I can see where the set lives.

CUBERANKEDMEMBER()

Now I write a CUBERANKEDMEMBER() formula:

CUBERANKEDMEMBER() Wrapped in an IFERROR()

Notes:

1. I then fill this formula down enough cells to handle every slicer tile (about 26 cells down in this case)
2. The IFERROR is used to handle the case where the user has not selected that many slicer tiles – CUBERANKEDMEMBER returns an error when you “fall off the edge” of the user’s selections.  This returns a nice blank cell instead.
3. ROW(A1) returns 1, and when I fill the formula down, I get ROW(A2), ROW(A3), etc. – so that each cell grabs the first, second, third, etc. values from the set of user selections.
4. $M$1 is the cell where I created the CUBESET() formula, and I do not want that reference to autoadjust.

Tying it Together

Now you can use other formulas to maybe concatenate all selections into a single, comma-separated string.

Or in my case, you can also apply conditional formatting to the cells so that they “light up” when there’s a selection.  In this case, I leveraged the “Unique Values” flavor of CF, since all slicer tiles always have unique names:

Note that you need to make sure you always have at least two blank cells in your CF rule, otherwise when the user selects all slicer tiles but one, the single blank CUBERANKEDMEMBER formula WILL get formatted

Dealing with No Selection

If the user makes no selection, you get this:

That’s probably ok in most cases.  There are advanced tricks you can use to fetch each individual value in this case if needed, but off the top of my head I don’t remember what they are.  I’d have to go look at the Calendar Chart again.