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

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

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 Smile

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 Smile


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

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.


Now I write a CUBERANKEDMEMBER() formula:




  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 Smile

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.

  Subscribe to PowerPivotPro!


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 41 Comments

    1. 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. (But i think you have to change the first CUBESET argument to something different – “PowerPivot Data” doesn’t work in 2013 I think).

          1. 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).

  1. 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
    _yearsSlicer: =CUBESET(“PowerPivot Data”,Slicer_FiscalYear,”Slicer Years”)

    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)

    =IFERROR(IF(CUBERANKEDMEMBER(“PowerPivot Data”,_yearsSlicer,1)=”All”,
    CUBERANKEDMEMBER(“PowerPivot Data”,_yearsDim,ROW()-ROW($D$30)+1),
    CUBERANKEDMEMBER(“PowerPivot Data”,_yearsSlicer,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


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

  2. 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?

  3. 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. 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. 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.

  4. 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. 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. 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. 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.


  5. 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.

  6. 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. Don’t worry, I found the issue.In the PowerPivot data, a different date format needs to be selected (e.g. yyyy-mm-dd)

  7. 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.

  8. 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:

  9. 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?

  10. 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

  11. 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! 🙂

  12. 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!

    1. Travis, did you ever find a solve for your scenario? I have the same problem. Trying to create industry averages based on stores selected without filtering on an industry slicer

      1. Hi Kris,
        I hope I can recall exactly what I did… :). I ended up thinking outside of the box to solve the problem a different way.

        My report has a pivot table that consists of just the name of the stores as Filter, and the store IDs as VALUE. I hid these two rows of the pivot table (the Column title (ID) and the value below it) just because I didn’t need it to appear on my report. I now have a drop-down of each of the stores that when selected will change the ID value, to which I can refer to in formulas later. I created a slicer on the Stores Name (Slicer_Name), and associated it with the Pivot Table, which can then also be referred to in calculations. I didn’t really need the slicer to appear on my report since I have the drop down to change stores, so I hid the slicer from view as well.

        Now I’m able to use a cube formula such as:

        =N(CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Sum of PriceExclTax]”,Slicer_Name,”[OrderItemProductOrder].[NewProductType].[“&B21&”]”,Slicer_Quarter_Year))

        which uses the value of Slicer_Name. As well, I can call that Store ID


        which here is referred to as cell $A$11.

        I hope this helps a bit!

  13. 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!

  14. Hi Rob,

    I have been searching all over for days and haven’t found anyone ask this question anywhere yet. This is the best article I’ve come across yet though, and it’s technique is getting used frequently in our org, so thank you!

    Here’s my question. Is there a way to combine multiple CUBESETs into one, specifically by referencing multiple slicers. For instance, I want to return the CUBERANKEDMEMBER (Acct Name) from a set that is filtered by two slicers (Acct Zone and Mega Group). I can’t quite figure out the syntax.

    I know I can reference multiple dimensions in the CUBESET formula, such as this:
    =CUBESET(“QPR_CoreModel”, “([Dim_Client].[Acct_Zone].children, [Dim_Client].[Group_Mega].children, [Dim_Client].[Acct_Name].children)”, “Clients, All Zones and Groups”)

    The above works, but this returns 0 accounts:
    =CUBESET(“QPR_CoreModel”, “( {“&Slicer_Dim_Client&”}, {“&Slicer_Dim_Client1&”}, {[Dim_Client].[Acct_Name].children} )”, “Clients, All Selected Zones and Groups”)

    Even though this works:
    =CUBESET(“QPR_CoreModel”, Slicer_Dim_Client, “Zone”)

    I would like to end up with this essentially:
    =CUBERANKEDMEMBER(“QPR_CoreModel”, (Clients, All Selected Zones and Groups), 1)

    Can you point me in the right direction? I’ve been playing with the syntax forever and a day…

    And thank you for your blog!


Leave a Comment or Question