Skip to content
March 23, 2015 / Erik Svensen

Get selected items in a slicer in Excel 2016

My most popular post so far has been “How to get selected items in a slicer without VBA” – Link – and after reading Chris Web’s post about the new Excel 2016 ConcatenateX function in DAX – link – I thought that this function could be used to calculate the selected items in a slicer as well.

And it turned out the function is the perfect solution to do this – and very easy to implement

First I created two simple tables – one with sales and one product table

Add it to the data model via the PowerPivot tab – and I add a calculated measure Sales Value as SUM([SalesValue]).

Now Add a Pivottable and a slicer to filter the table by Product.

Now I add a calculated measure to calculate the selected items

Adding the measure to the pivottable will result in this

But you might want it as a measure in the pivottable but the value stated in a single cell.

This can be done by using the CUBE functions – Use the following formula to accomplish this

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Selected Products]”,Slicer_Product)

The two member expressions in the formula refers to the measure and then the name of the slicer.

It has never been easier to retrieve the selected items from the slicers with the new CONCATENATEX function..

You can download the example file here

Advertisements

5 Comments

Leave a Comment
  1. hv / Mar 31 2015 11:20 am

    dear erik
    many thks for yr very usefull tips, is it please possible to have yr examples in excel 2013
    many thks on beforehand

    • Erik Svensen / Mar 31 2015 11:40 am

      Hi – The CONCATENATEX function is only available in Excel 2016 – but we might get it in an update to Excel 2013 – lets hope so
      BR
      Erik

      • hv / Mar 31 2015 1:27 pm

        dear Erik , many thks for yr prompt reply , would be great also to have the update in excel 2010 … we are running so far behind … unfortunately …
        have a nice day and all the best for you – hv

Trackbacks

  1. Das neue Excel 2016 | Teil 2: Power Pivot | Linearis :: BI für die Fachabteilung
  2. Finding All Selected Items In A Slicer In Excel 2016 Using TextJoin() – Chris Webb's BI Blog

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: