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:

Power BI Slicer with a Card "Readout" Telling Us What was Selected

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

  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”

Not Just for Slicers!

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

Power BI Treemap with a Card "Readout" Telling Us What was Selected

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

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 16 Comments

  1. Awesome post Rob! So you’re telling me that Tree Map allows for dynamic captions?! Are there any other visuals that support this?

    Dynamic titles or subtitles would be awesome, here’s hoping this becomes more widespread

  2. If there are more than 5 then it looks better if we Display it as “Excel, PowerQuery, Access, PowerPivot, PowerBI Desktop…..”

    So we can define 2 Measures

    mConcTxt:= CONCATENATEX(ALLSELECTED(PRODUCTS[PRODUCT]),PRODUCTS[PRODUCT],”,”)
    and
    mSelected = IF(NOT(ISFILTERED(PRODUCTS[PRODUCT])),”All”,IF(COUNTROWS(ALLSELECTED(PRODUCTS[PRODUCT]))>5,MID([mConcTxt],1,FIND(“|”,SUBSTITUTE([mConcTxt],”,”,”|”,5))-1) & “…..”,[mConcTxt]))

  3. Rob, as usual, your timing is perfect! This is just what I needed today to help someone today.

    For a different case, handling of “too many subcategories”, could be to concatenate the “TOPN” 5 and append an ellipsis, as:

    [Referenced Subcategories] :=
    IF (
    NOT ( ISFILTERED ( Products[Subcategory] ) ),
    “All”,
    IF (
    COUNTROWS ( ALLSELECTED ( Products[Subcategory] ) ) > 5,
    CONCATENATEX (
    TOPN ( 5, ALLSELECTED ( Products[Subcategory] ), Products[Subcategory], 1 ),
    Products[Subcategory],
    “, ”
    ) & “, …”,
    CONCATENATEX (
    ALLSELECTED ( Products[Subcategory] ),
    Products[Subcategory],
    “, ”
    )
    )
    )

  4. Fantastic article Rob! This can be super useful especially if someone takes a screen shot or otherwise uses an image of a report and wants to include what the filters are without having to show the slicers in the image.

  5. If instead of ‘All’ specific available values need to be represented can use CONCATENATEX measure as follows:
    1) Create another power pivot table that would have CONCATENATE measures as values;
    2) Use CUBEVALUE or GETPIVOTDATA to extract and list all values available from the specified slicer

    1. Hi Maxim,

      ALLSELECTED(table[column])
      corresponds to
      CALCULATETABLE(FILTERS(table[column]), ALLSELECTED(table[column]))

      And

      FILTERS(table[column])
      In most cases corresponds to
      CALCULATETABLE(VALUES(table[column]), ALLEXCEPT(table, table[column])

      So if you use FILTERS(table[column]) instead of ALLEXCEPT(table[column]) in a visual that doesn’t create a filter context of it’s own, like the card visual Rob is using in the blog post, there will not be any difference. If you however use FILTERS inside a visual that DOES create a filter context of it’s own (like a table, matrix etc.) there MAY be a difference.

      ALLSELECTED(table[column]) is a direct filtering function
      ALLSELECTED(Table) is a cross filtering function

      FILTERS(table[column]) is a direct filtering function
      VALUES(table[column]), VALUES(table) are cross filtering functions.

      FILTERS(table) doesn’t exist because it doesn’t make sense. When you have more than one column to consider you are automatically cross filtering.

      Since most visuals are populated using cross filtering you most often would use functions reflecting this. See @Jorge question above. If you use direct filtering you can end up in a situation where what you see selected in slicers does not correspond to what you see shown in a visual. This can happened when slicers and filters cross filter each other.

      Best regards
      Jes.

Leave a Comment or Question