The Latest and Best Way to Catch/Capture/Inspect Slicer Selections

Quick post today because, well, even more going on than usual.

This is actually the fifth post on this specific topic, which means that it’s something that keeps coming up.  But unlike previous posts, in which we kept DISCOVERING slightly better ways to do things, this latest post is triggered by us GETTING a new way to do it from Microsoft.

Yes, that’s right, this one will not work in previous versions of Excel, because we’re gonna use CONCATENATEX!

I’m gonna use Power BI Desktop in this post for the simple reason that I’m on my laptop, which is still running Excel 2013.

OK, let’s start with a Power BI Slicer, and a Card displaying a fancy (but simple!) measure that I’m going to share:

image thumb 10 The Latest and Best Way to Catch/Capture/Inspect Slicer Selections

A Power BI Slicer and Card
(But What is the Card Displaying?)

In its simplest incarnation, this measure is…  super simple.

[Selected]:=

CONCATENATEX(
ALLSELECTED(Products[Subcategory]),
Products[Subcategory],
“, ”
)

That’s it!  And then you put the [Selected] measure on your card.

In Excel, the DAX is the same.  To display it, you would simply put it in a cell using CUBEVALUE, something like:

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Selected]”,Slicer_Subcategory)

(Where Slicer_Subcategory needs to be changed to the name of the slicer YOU are using, obviously.)

The original measure above is really awkward when the user has made NO selection on a slicer – because it can then return a REALLY long list!

To deal with that case, we add an IF to the measure to detect precisely that case, and then return “All.”

[Selected]:=

 IF(NOT(ISFILTERED(Products[Subcategory])),”All”,
CONCATENATEX(
ALLSELECTED(Products[Subcategory]),
Products[Subcategory],
“, ”
)
)

[Selected]:=

IF(NOT(ISFILTERED(Products[Subcategory])),”All”,
IF(COUNTROWS(ALLSELECTED(Products[Subcategory]))>5, “> 5 Subcategories”,
CONCATENATEX(
ALLSELECTED(Products[Subcategory]),
Products[Subcategory],
“, ”
)
)
)

  1. Whatever column you’re going to use as your slicer, you need to change the measure to reference that (as opposed to Products[Subcategory] in my example).
  2. 5 is not some holy limit – feel free to set your own there, or omit that entire second IF in the case of slicers with very few values.
  3. Obviously feel free to play with the delimiter in CONCATENATEX – I used “, “ but you can do whatever you want.
  4. Same thing with the text values for “All” and “>5 Subcategories”

You can also use this in combination with other visuals, such as Treemap:

image thumb 11 The Latest and Best Way to Catch/Capture/Inspect Slicer Selections

Remember:  You Can Use Other Visuals as Slicers Too, and this Technique Still Works
(Doubly useful IMO since it’s often difficult to tell what’s selected in a Treemap by just looking)

Download the PBIX

X

Get Your Files

Let’s block ads! (Why?)

PowerPivotPro