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.
CONCATENATEX – Requires Excel 2016 or Power BI Desktop
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.
Setup (Power BI Version)
OK, let’s start with a Power BI Slicer, and a Card displaying a fancy (but simple!) measure that I’m going to share:
A Power BI Slicer and Card
(But What is the Card Displaying?)
Measure: Step 1
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.
What About Excel? It Doesn’t HAVE Cards!
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.)
Measure Step 2: Dealing with No (aka All) Selections on Slicer
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],
“, ”
)
)
And Now, Step 3: Dealing with “Too Many” Specific Selections
[Selected]:=
IF(NOT(ISFILTERED(Products[Subcategory])),”All”,
IF(COUNTROWS(ALLSELECTED(Products[Subcategory]))>5, “> 5 Subcategories”,
CONCATENATEX(
ALLSELECTED(Products[Subcategory]),
Products[Subcategory],
“, ”
)
)
)
Some Obvious Stuff
-
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).
-
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.
-
Obviously feel free to play with the delimiter in CONCATENATEX – I used “, “ but you can do whatever you want.
-
Same thing with the text values for “All” and “>5 Subcategories”
Not Just for Slicers!
You can also use this in combination with other visuals, such as Treemap:
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)
