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
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
And modify the fomula in selected weeks to
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.