skip to Main Content

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.


“, ”

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:


(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.”


“, ”

And Now, Step 3:  Dealing with “Too Many” Specific Selections


IF(COUNTROWS(ALLSELECTED(Products[Subcategory]))>5, “> 5 Subcategories”,
“, ”

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


Get Your Files

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 23 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

    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] ) ),
    IF (
    COUNTROWS ( ALLSELECTED ( Products[Subcategory] ) ) > 5,
    TOPN ( 5, ALLSELECTED ( Products[Subcategory] ), Products[Subcategory], 1 ),
    “, ”
    ) & “, …”,
    ALLSELECTED ( 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,

      corresponds to
      CALCULATETABLE(FILTERS(table[column]), ALLSELECTED(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

  6. Hi Rob
    Wanted to check with you is there a way that based on the slicer value I display different reports on the same page in Power BI

    Say I have 3 report pages – Dashboard, Trend Charts, Detail Table Reports
    I create a 4th page with a slicer to contain the page I want to go to
    If the user clicks 1 in the slicer it should take me to Dashboard page
    If the user clicks 2 then to trend charts page


  7. This is sooooo helpful. I have a question about doing this for the Category as well. I have a spreadsheet that has the Category and Subcategory int he same table and a slicer for each. I was able to get the Subcategory to work, but when I try this for the Category, it repeats each category for all existing Subcategories. How do I get unique Categories using this approach?

  8. Doesn’t work for me. I am getting ‘>5 selected’ even when I am not filtered anywhere.

    PS- I am using slicer to filters with bookmarks. So what I am doing is using a button which uses a bookmark to show me the slicer, and then I use that slicer to filter stuff.
    But even when I am not filtered, it still shows me ‘>5 selected’. I suspect it just cannot see that I am not filtered and is interpreting the first condition as false and goes to the second condition to show me >5 selected’.

    Please help.

  9. I have the same req. as @tony, need to get date slicer with the ‘between’ feature. Any help much highly appreciated in advance.

  10. Interesting article.
    In the intro you say this is the 5th on the subject. Great! Could you please update the article, or even here in the comments, add links to the 4 earlier articles. I’d like to see what the earlier techniques are since there are still people using versions before 2016. It’s a drag playing hide and seek in your lists of articles …

    Keep up the good work.

  11. Hi Rob
    Thanks for the great post. I used your technique in my dashboard. I captured all values selected in my slicers and showing these values on Card visual. But my data is not sorted on card i’e years come like 2018,2020,2019, Months comes like Aug,Oct,Sep,Apr,Dec,Feb…..

    How can I resolve this? How can I show data on card in sorted order.

    I really appreciate any help.


    Touheed Ahmed

Leave a Reply

Your email address will not be published. Required fields are marked *