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)

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:

  1. http://www.powerpivotpro.com/2010/06/use-slicer-values-in-a-calculation-with-powerpivot-dax/
  2. http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/ 
  3. http://www.powerpivotpro.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 Smile

CUBESET!

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

image

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

CUBERANKEDMEMBER()

Now I write a CUBERANKEDMEMBER() formula:

CUBERANKEDMEMBER() Wrapped in an IFERROR()

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:

image

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:

image

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.

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

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

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

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

      • 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
    _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

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

  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.

  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.

    • 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?

      • 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

    • 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. jeffrey Weir says:

    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

    • 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

Leave a Comment or Question