Skip to content
August 23, 2012 / Erik Svensen

How to get selected items in a slicer without VBA

Slicers is an essential tool in Excel when working with power pivot pivot tables (and “normal/old” pivot tables). But when connecting the same slicers to multiple pivot tables and perhaps place all your slicers on a separate sheet you have to be able to show what your model users have selected in the slicers when they look at the pivot table.

You can create VBA code to read the selected items from the slicer object, but this code cannot be run if you publish your model for use in Excel Web App on SharePoint.

So to avoid code I have created formula to generate a text string indicated what the user selected in a slicer.

Step 1 Create the data for a powerpivot model

 

First I create some sample data to use in a powerpivot model.

The Store =INT(RANDBETWEEN(1;50))

The date =TODAY()-RANDBETWEEN(1;100)

The Product =RANDBETWEEN(1;10)

The Sales Value =RANDBETWEEN(100;4000)

And then copy these formulas to generate a random number of rows.

Step 2 Create a linked table to PowerPivot

 

Select PowerPivot and click Create Linked Table

This will link the data to PowerPivot so we can build a simple model.

In order to create a year and month slicer – I add to calculated columns.

Year: =Year([Date])

Month: =Month[Date])

Step 3 Create a pivottable with slicers

 

Then I setup a pivot table with slicers that might look like this.

 

Step 4 Let’s calculate the selected items in the Store Slicer

 

A slicer in Excel has some settings in which you can identify the slicer in your formulas.

 

In this case our slicer “Store” is called “Slicer_Store”.

We can use this name in our formulas.

In order to get the number of selected items in a slicer we use the function – CUBESETCOUNT

 

The formula =CUBESETCOUNT(Slicer_Store) will return 1 – as all items is selected

 

 

When you select three items it will return three.

Next function to use is CUBERANKEDMEMBER-

 

CUBERANKEDMEMBER can return a member in a set by specifying it rank in the set.

Using the name of the slicer – the name will return a set of selected items in slicer !!!!!!!!

=CUBERANKEDMEMBER(“PowerPivot Data”;Slicer_Store;1)

So this formula will return the first selected item in the slicer and if all items is selected it will return All.

 

Step 5 Lets create a label for the selected items

 

A slicer can contain many items and I don’t want to create a very long label, I chose to let the label contain the first 10 selected items and if more than 10 is selected – the label should return the text “More than 10 selected”

 

In column M there is a rank number from 1 to 10.

In column N next to the rank I create a formula that retrieves the ranked member in the slicer.

=IF(M14<=$N$12;CUBERANKEDMEMBER(“PowerPivot Data”;Slicer_Store;M14);””)

The formula test if the rank is lower or equal to the number of selected items in the slicer (calculated with CUBESETCOUNT), if it is – it will return the ranked member in the Set – otherwise it will return blank text.

In column O I create formulas to generate the label.

First formula just takes the first member

=N14

Second item

=O14&IF(N15<>””;”, “&N15;””)

Tests whether the N columns returns an result – if it does it will concatenate the two labels split by a comma.

This formula is copied down for all rank numbers.

And finally a formula to create the complete list or if more than 10 is selected

=IF(N12>10;”More than 10 selected”;O23)

The cell is then named “StoreSelected” and then you can refer via the name on any of your sheets in the workbook.

Try it

 

You can download the example here – Link (open the file in Excel – not in Excel web app as powerpivot isn’t supported )

Advertisements

10 Comments

Leave a Comment
  1. Floyd Johnson / Nov 28 2012 2:54 am

    This works really well. Thanks for the example. Can you take the example one step further, by showing how to determine what the linked values of the selected month would be when the stores selected is 3,4,9? In you sample display i see that month 5 is not intersecting when the stores selected are 3,4,9. It is grayed out. Without using VBA is it possible to see that month 5 is not intersecting? Does that require custom MDX using except()?

    • Erik Svensen / Nov 28 2012 9:00 pm

      Hi Floyd,

      Good question – as you mention it is possible with VBA with the use of the VisibleItems object.

      But without VBA is a bit more tricky…

      If you only have unique member selections in stores and products you can create a CUBESET with

      =CUBESET(“ThisWorkbookDataModel”;”CROSSJOIN([Sales].[Product].&[2], [Sales].[Store].&[15],[Sales].[Month].Children)”)

      and the CUBESETCOUNT to return the numbers of members the CUBESET returns

      =CUBESETCOUNT(CUBESET(“ThisWorkbookDataModel”;”CROSSJOIN([Sales].[Product].&[2], [Sales].[Store].&[15],[Sales].[Month].Children)”))

      This will return the number of items that the CROSSJOIN returns.

      PS the [Sales].[Month].Children must be the last member

      Then use CUBERANDKEDMEMBER to retrieve the ranked items

      =CUBERANKEDMEMBER(“ThisWorkbookDataModel”;CUBESET(“ThisWorkbookDataModel”;”CROSSJOIN([Sales].[Product].&[2], [Sales].[Store].&[15],[Sales].[Month].Children)”);1)

      Hope this can help you

      /Eri

  2. Lina / Jul 25 2014 7:26 pm

    Thanks so much, this was incredibly helpful. I did have a question though. You mentioned in the beginning that it is possible to create VBA code to read the selected items from the slicer object. Could you explain how to do that, or link me to another source that explains how? I’ve been looking everywhere for code that can read selected slicer items, but can’t find it anywhere. My data is linked to PowerPivot. Thanks!

  3. Pedro / Sep 20 2017 3:34 pm

    Thanks a lot Erik Svensen!
    It is a 5 years old post but saved me a lot of time today.
    Best regards,
    Pedro

Trackbacks

  1. Using Slicer Selections In The CubeSet Function | Chris Webb's BI Blog
  2. Using Slicer Selections In The CubeSet Function - SQL Server - SQL Server - Toad World
  3. Using Slicer Selections In The CubeSet Function | Gobeyo
  4. Get selected items in a slicer in Excel 2016 | Erik Svensen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: