How to list only selected slicer items in separate slicer

My most popular post so far has been “How to get selected items in a slicer without VBA” and I have demoed the solution at SQL Saturday and Campus days in Copenhagen.

This solution makes heavy use of cube functions and can be fairly complicated to construct and maintain – so I decided to see if it was possible to do try and see if I could list the selected items in a separate slicer.

So the slicer “Selected weeks” lists the selected items and I can actually copy this slicer to all the sheets where I have reports that is connected to the first slicer “Week”.

Here is how I did this

First Step – Create a DAX Query table

In my workbook with my powerpivot model I added a DAX Query table to the time table via Data tab – Existing Connections and found the Time table in my list of tables

Clicked on Open and selected a Table

This gives me a standard Excel table linked to the Power Pivot model as a DAX Query.

 

Calculate selected items

 

Next to the table I added a column called “Selected Weeks”.

And I added the following formula

=IF(ISNUMBER(CUBEVALUE(“ThisWorkbookDataModel”;Slicer_YW1;CUBEMEMBER(“ThisWorkbookDataModel”;”[Time].[Week].&[“&[@Week]&”]”);”[Measures].[Sales Value]”));[@Week];””)

The formula uses the CUBEVALUE function to calculate the Sales Value for the Slicer “Slicer_YW1” and the current member of [Time].[Week] – if the current week intersects with one of the selected items in the slicer – then it will return a value/number and if so – the formula will return the current week label – otherwise a blank text.

Create the Slicer

Then I can use the “Insert Slicer” on the table to insert a slicer for the column “Selected weeks”

 

And I get a slicer containing the selected weeks in the primary slicer

Clean up the blank

As all the rows that wasn’t selected items is set to return a blank – the slicer will have an item with a blank item – this can be avoided as well.

First of this table should only be used for this purpose so we actually don’t care what the blank value is – and as we now that slicers return unique items we can instead of returning a blank item – we can return one of the selected items.

So above the table I create a formula to get the first selected item in the primary table.

By using the formula

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”;Slicer_YW1;1)

And modify the fomula in selected weeks to

=IF(ISNUMBER(CUBEVALUE(“ThisWorkbookDataModel”;Slicer_YW1;CUBEMEMBER(“ThisWorkbookDataModel”;”[Time].[Week].&[“&[@Week]&”]”);”[Measures].[Sales Value]”));[@Week];$F$2)

And the blank member is now gone

 

And I have a nice clean slicer with the unique items selected in the “Power Pivot” slicer – and as this is linked to a standard Excel table – I can copy this to all my report sheets where I want to display the selected weeks – that I typically let them select on a central Filter/Slicer Sheet.

 

 

 

Advertisements