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:

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.

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 38 Comments

1. This actually works with all OLAP pivot tables, but not with slicers linked to a table in Excel 2013 Preview.

1. powerpivotpro says:

Laurent – a normal pivot will not work in 2013. But if you use the Data Model version of a pivot in 2013, it should.

1. powerpivotpro says:

(But i think you have to change the first CUBESET argument to something different – “PowerPivot Data” doesn’t work in 2013 I think).

1. Yes, at least in the Preview version of Excel 2013, the first argument should be “ThisWorkbookDataModel”.

1. Jeremy says:

Thanks Laurent. This is exactly what I was looking for – insidious change for converting from 2010 to 2013.

2. Exactly, and it will also not work with slicers linked to plain tables (a new feature of 2013).

I meant the trick will also work with slicers connected to pivot tables based on an OLAP data source, such as plain old SSAS cubes (multi-dimensional models).

2. David Pitts says:

Awesome – Just what I have been looking for – Thanks

3. Bob Phillips says:

When I do any presentations on CUBE formulae, I show this way of showing slicer values. In my example, I have a slicer of fiscal years, sourced from a dimension called DateTime.

First, I create a set of slicer selections, and name the cell

Then a similar set from the dimension
_yearsDim: =CUBESET(“PowerPivot Data”,”[DateTime].[FiscalYear].Children”,”Dimension Years”)
Note that it is getting the children of the [DateTime].[FiscalYear] member, so it gets all values regardless of any selections.

I then add a CUBEREANKEDMEMBER formular similar to Rob’s, but I cater for the slicer set returning All, and pick up the dimension set in that case (note that my first formula is in D30 which is the relevance of D30 in the formula)

CUBERANKEDMEMBER(“PowerPivot Data”,_yearsDim,ROW()-ROW(\$D\$30)+1),
“”)

This is saying that if the slicer cubeset returns ‘All’, get the next item from the dimension set, otherwise just get the next item from the slicer set. The standard IFERROR wrapper is added to allow for flex in the list.

This enables the list to fully reflect the slicer selection, and show all values when no selection filtering is done.

One other small point that I always do. Rather than have the connection hard-coded into every formula, I enter that value in a cell, name the cell, and use the cell name in my formulae. So, if I enter ‘PowerPivot Data’, without the quotes in a cell and name that cell _cube, I then use

CUBERANKEDMEMBER(_cube,_yearsDim,ROW()-ROW(\$D\$30)+1),
“”)

This also helps migrating to 2013, I just have to change the value of cell _cube to ThisWorkbookDataModel, rather than modify all my formulae.

4. Michael Shparber says:

This is just SO COOL!!!
Thank a lot for this! Just got some ideas for implementing!
Michael

5. rob says:

Great function! Quick question – is there a way to get what is NOT selected with a similar formula?

1. powerpivotpro says:

Great question. That’s one for Bob P, Dick M, or David H I think.

6. Rob A says:

OK, as long as I’m asking….

I got the function to work initially with a few selections – as soon as I added a few more it started returning #na. Anyone seen this behavior before?

7. Pete Gentile says:

When I use the CubeSet formula – =CUBESET(“PowerPivot Data”, Slicer_Whse_Zone_Desc,”This is My Set”) – I get an error #Name? – what am I doing wrong? Slicer_Whse_Zone_Desc is the slicer name.

1. powerpivotpro says:

Are you using 2013? In 2013, “PowerPivot Data” is now “ThisWorkbookDataModel”

1. Evan says:

I get the same error and I am using 2010…

2. Jikke says:

Exactly the same issue here. I used =CUBESET(“ThisWorkbookDataModel”;Slicer_age3;”This is My Set”) and am also getting the #NAME? error. Has anybody found a fix for that yet?

1. MB says:

Not sure why this was the case, but I found that removing the caption portion of the formula worked. For kicks I reentered the caption and that formula now works.

8. David Betts says:

I want to use one slicer to control pivot tables from different data sources which cannot be linked. For example i have customer shipment data and customer price data and I want to user a customer slicer to change both tables. So in effect i want to reproduce the filtering action on a second table. Any way to do this? DAve.

1. powerpivotpro says:

Create a single Customers table, create relationships between it and both the Price and Shipment tables, then use a column from the Customers table as a slicer?

1. David Betts says:

Thanks. This works. But you end up with a query with lots of blocks of data that are unrelated and lots or records with blanks in them. And you need distinct field names for the same fields in the different tables. If you get to several tables it is cumbersome. And you have to update the records in the customers table every time new customers are added. Far better if there was a way of taking the values in one slicer and replicating them in another? Dave

2. Steve says:

Reminds me since I also often want to using a single slicer for multiple tables from varied sources that cannot be linked. Here’s one way that I found to work in just simple cases. Are there other ways that anyone can share without using VBA?

1) Create a slicer “Slicer_ABC” – choose a primary table table1 or create a new table to use as a control slicer;
2) Create a CUBESET for table2,3 etc based on the CUBESETCOUNT result from “Slicer_ABC”. in this example, there are only two possible values: 1 = All meaning no items selected, or either A or B or C; 2 = (A & B, A & C, B & C); total of 7 slicer combos.

IF(CUBESETCOUNT(Slicer_ABC)=1,
CUBESET(“ThisWorkbookDataModel”,”{[TABLE2].[ABC].[“&CUBERANKEDMEMBER(“ThisWorkbookDataModel”,Slicer_ABC,1)&”]}”,”1″),
CUBESET(“ThisWorkbookDataModel”,”{[TABLE2].[ABC].[“&CUBERANKEDMEMBER(“ThisWorkbookDataModel”,Slicer_ABC,1)&”],[TABLE2].[ABC].[“&CUBERANKEDMEMBER(“ThisWorkbookDataModel”,Slicer_ABC,2)&”]}”,”2″))

9. My approach is to just test if the first thing in a hidden pivot is the same thing as the last thing:
=IF(INDEX(A:A,COUNTA(A:A)) does not equal A2,”Please select a single country”,””)

Note that you have to turn off Grand Totals, or adjust the above to accommodate them.

10. Kim says:

Thanks for the above. I performed this on my date slicer, but the results have converted my dates in to MM/DD/YYYY. Is there an issue with CUBE commands not using localisation or is there a parameter I’m missing?

Note: NZ and AUS use DD/MM/YYYY

1. Kim says:

Don’t worry, I found the issue.In the PowerPivot data, a different date format needs to be selected (e.g. yyyy-mm-dd)

11. Bob says:

This technique works well for regular slicers. Thanks for posting Rob!
Has anyone perfected something similar for a Timeline slicer?
This technique does return values but they are independent of the Time Level selected.
If the Time Level is ‘YEARS’ then I would like to show “2010-2014”.
But if the Time Level is ‘QUARTERS’ then I would like to show “Q1 2011 – Q4 2014”.
I’m essentially trying to duplicate the display of the Selection Label without using VBA as I will be publishing to SharePoint/Excel Services.

12. Bertrand says:

There is a 100% DAX variant solution to this problem that a couple of colleagues and myself designed. It involves associating a prime number to each slicer value, and a metric that multiplies the primes of all selected values. If the division of this total value by one of the prime number is an integer, then it means that the corresponding slicer value is selected. Based on this, and for each slicer value, we create a corresponding boolean metric that checks whether this value is selected not. The difficult part is that there is apparently no DAX function that performs the multiplication of a whole table column. But this can be worked around by using LOG and POWER functions ( log(X) + log(Y) = log ( X * Y ), POWER(10,LOG(X))=X ).
1. Create a disconnected table with the labels to be selected (Table[Labels])
2. Add a column with distinct prime numbers for each row (Table[Prime])
3. Add a calculated column that calculates the LOG of the prime column (Table[LogofPrime]).
4. Create a measure: [Sum of LogSelected]:=SUM(Table[LogofPrime])
5. Create a measure : [Product of selected values]:=INT(POWER(10,[Sum of LogSelected]))
6. Create test metrics for each slicer value: [test if A selected]:=MOD([Product of selected values],primeofA )=0 where primeofA is the prime corresponding to A in ‘Table’
Check the workbook here: http://1drv.ms/1uKhXPw

1. I cannot get your solution to work. Are there any steps after 6?

2. Please disregard my previous post, as your solution executed perfectly! Thank you so much! 🙂

13. Paulo Barata says:

HI there,

I`m working with Office professional Plus 2013 and I can’t work the CUBE formula. I’m using the following formula: =CUBESET(“ThisWorkbookDataModel”;Slicer_B1___Mercado;”text”) and I get #N/A as result. Can you tell what is my error?

1. Hi Paulo. I found that if you don’t add the Pivot to the data model then you get the n/a result.

1. Gerson dos Santos says:

How do i add the Pivot table to tha data model? In my case, the pivot is in another worsheet.

14. Vivek says:

If the slicer has only 1 value (for a user with security restrictions when connected to a SSAS Tabular model) then how can I get that 1 value. It only shows All.

Also, instead of using CUBEMEMBER and then using CUBERANKEDMEMBER, directly putting the slicer name in the CUBERANKEDMEMEBER formula seems to be yielding the same results as well
=CUBERANKEDMEMBER(“CONNECTION NAME”,Slicer_Name,ROW(A1))

15. Great post Rob!

I am wondering how do I get the count of selected items in a slicer. I intend to use the count in a calculated column contained in a data model.

For example:
I have two unrelated tables; a calendar table showing months from 2014 until 2020, and a person table showing people demographic data.

If I create a slicer based on distinct people and call it Slicer A, how do I return the count for items that are selected.

[Slicer A]
Person 1
Person 2
Person 3
Person 4
Person 5

If I selected Person 1, Person 3, and Person 5, the total count would be 3; which I will use in a calculated column on my calendar table where 3 would appear for every month in the table.

I have looked everywhere, and have tried in vain multiple suggestions/solutions e.g. Countx/Sumx, AllSelected, IsFiltered, et al. However I continually return the overall total instead of a count of selected items.

Any insight will be greatly appreciated!

Thank you so much for your blog! It’s required reading for me, entertaining, and very well done! 🙂

1. Please disregard my previous post, this solution works perfectly, thank you so much!

16. Travis says:

Hello –
This is a great post (4 years running and still generating lots of interest!!). Here’s my situation.

I have my pivot table on the active worksheet. I am able to use CUBESET and CUBERANKEDMEMBER to return the value of the filter in the pivot table perfectly. What I would like to do is return a different column in the table of this data model that does not appear in the pivot table. The table is STORES. My filter value is the name of the store, but I need to have the ID to be able to use in future calculations. I added a slicer (Slicer_Id) that has the IDs, and as I changed the name values in the filter, the selection in Slicer_Id updates, however using CUBESET and CUBERANKEDMEMBER only returns “All”. How can I return that value that shows in Slicer_Id?

Thanks very much for your help!

17. Hi guys, very useful post. I’m concurring with Bob above, do you perhaps have the same fix for Timeline Slicer? Thank you in advance!

1. Brian Wilson says:

Ditto to Lana! Timeline would be great

18. Kelly says:

Hi! Is it possible to show a Slicer name in a Formula?