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