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