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:

I then fill this formula down enough cells to handle every slicer tile (about 26 cells down in this case)

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.

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.

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

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.

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)

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

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.

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

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.

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?

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.

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

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?

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

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

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?

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

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”)

How is it possible to have 2 slicers that use the same data field to work independently to populate different CUBESET formulas on the same worksheet. In your example you have at least 2 slicers for the “Description” field (Slicer_Description3 as shown and presumably a Slicer_Description2) – so that these can act independently and create different sets. The problem I have is that if I add 2 slicers for the same data field the “Name to use in formulas:” is always the same and so the selection made with the 1st slicer is replicated in the 2nd slicer. How did you get the Slicer_Description3?

Im just starting to use Powerpivot and now CUBEVALUE and have found your blog/posts invaluable in learning these great tools

Your post, while fresh relative to the total vintage of this thread, did accumulate some age, so you may already have resolved your issue, but I’ll post mine so it might benefit others with the same challenge. Posted solutions on this website have accelerated my learning curves immensely to a number of power pivot skill sets I have sought to develop.

I have a worksheet that deals with the scenario you describe. My workbook data model concatenates monthly detail report data, and I mark the data loads in a field called “Report Month” in the format “YYYY-MM” as text. One of the output report pages compares the monthly movement for a number of parameters so, for that page I use two slicers: “Current Month” and “Previous Month” that filter on the same column to create the two sets of values to compare.

I am in the midst of converting this workbook to use cube formulas to stabilize the report presentations but I am struggling with getting the cubeset and cuberankedmember to work with multiple slicer selections, but that is for a different thread.

I had this two slicer routing working but as part of trying to conquer my cube challenges, I actually messed up what was working (two slicers independently selecting a value from the same column) to where they were not (now the two slicers kept matching each other when either one of the two was changed)

I managed to finally restore the functionality by deleting the current month and previous month slicers and recreating them individually (NOT creating a copy).
My observation was that in the slicer settings, I could change the Name and the header name, but not the “name to use in formulas”, and so long as that one remained the same in my two slicers, regardless of the Name and Caption values, the selections remained tied.

Recreating the slicers individually on the same column created two “Names to use in formulas”: My_Slicer and My_Slicer1. The moment I had two different “Names to use in formulas”, I could set the slicers to two values from the same table column, and the (lost) functionality was restored. I had to reconnect the slicers to the applicable pivot tables in the workbook, but that was a small price to pay.

Whu do I lways get a an error message when I use this “recipe”? #Name? is what i get when I use the CUBESET()-formula – and yes, I am using ““ThisWorkbookDataModel”” and have tried “PowerPivot Data”and the name of the underlying table, looked into connections etc. What to do?

I am fairly new to the use of power pivots and slicers, I’ve been using Excel, standard pivots and charts for years. Credit to designers and developers for the functionality in Excel, *amazing* and I’m sure i have only just scratching the surface.

Thanks to all contributors to this article, it has really helped me.

I needed to get a value from the slicer, this is the formula i ended up with and it works. I hope it helps someone else.

IFERROR – as suggested, I have wrapped the complete statement in an iferror function

(CUBERANKEDMEMBER(“ThisWorkbookDataModel” – if I’m honest I have no idea what this does, but it works for the version of excel i am using. There is an explanation in the help files, I just haven’t read and digested it yet.

,CUBESET(“ThisWorkbookDataModel” – again not totally sure what this does but it works!

,Slicer_Agegroup) my slicer name

,ROW(A1)) this returns the first selection, I’ve accommodated 2 choices for this slicer so cell in next row is Row A2 or they could be concatenated together.