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.
