topic The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever in Quick Measures Gallery

The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

Greg_Deckler — Fri, 01 Jun 2018 12:16:41 GMT

Sure, there are numerous dynamic title Quick Measures here in the gallery, but none are truly as mind blowingly dynamic and versatile as this one! This dynamic title measure understands all of the usual dynamic title things like everything selected, nothing selected, etc, but also understands direct and cross filtering context and can be easily modified to support any text formatting as well as display percentages, counts and simple text when picking multiple items.

Dynamic Slicer Title = 
VAR __ALLTEXT = "All"
VAR __NONETEXT = "No"
VAR __DIRECTFILTERPRETEXT = "You have chosen "
VAR __CROSSFILTERPRETEXT = "You have filtered down to "
VAR __POSTTEXT = " brand(s)."
VAR __CONCATENATE_TEXT = ", "
VAR __LASTCONCATENATE_TEXT = " and "
VAR __TOOMANY_MAX = 4
VAR __TOOMANY_PRETEXT = " and "
VAR __TOOMANY_POSTTEXT = " more"
VAR __USEPERCENT = FALSE() //Change to TRUE() to use percentages
VAR __PERCENTTEXT = " of"
VAR __PERCENTZEROTEXT = "0.00%"
VAR __USEMULTIPLECOUNT = FALSE() //Change to TRUE() to use counts after __TOOMANY_MAX is reached
VAR __USEMULTIPLECOUNTEXTENDED = FALSE() //Change to TRUE() to use "x of y" format after __TOOMANY_MAX is reached
VAR __USEMULTIPLECOUNTEXTENDEDTEXT = " of "
VAR __USESIMPLEMULTIPLETEXT = FALSE() //Change to TRUE() to use __SIMPLEMULTIPLETEXT after __TOOMANY_MAX is reached
VAR __SIMPLEMULTIPLETEXT = "Multiple" 
VAR __TOTAL_ROWS = COUNTROWS(DISTINCT(ALL('Table'[Brand])))
VAR __CURRENT_ROWS = COUNTROWS(DISTINCT('Table'[Brand]))
VAR __MAINTEXT = 
    IF(
        __USEPERCENT,
        VAR __PERCENT = DIVIDE(__CURRENT_ROWS,__TOTAL_ROWS,0)
        RETURN IF(ISBLANK(__PERCENT),__PERCENTZEROTEXT & __PERCENTTEXT,FORMAT(__PERCENT,"Percent") & __PERCENTTEXT),
        SWITCH(
            TRUE(),
            __CURRENT_ROWS = __TOTAL_ROWS,__ALLTEXT,
            __CURRENT_ROWS = 0,__NONETEXT,
            __CURRENT_ROWS = 1, MAX('Table'[Brand]),
            __CURRENT_ROWS < __TOOMANY_MAX,
                CONCATENATEX(
                    TOPN(__CURRENT_ROWS - 1,DISTINCT('Table'[Brand])),
                    [Brand],
                    __CONCATENATE_TEXT
                ) & __LASTCONCATENATE_TEXT & LASTNONBLANK(DISTINCT('Table'[Brand]),TRUE()),
            IF(
                __USESIMPLEMULTIPLETEXT,
                __SIMPLEMULTIPLETEXT,
                IF(
                    __USEMULTIPLECOUNT,
                    IF(
                        __USEMULTIPLECOUNTEXTENDED,
                        __CURRENT_ROWS & __USEMULTIPLECOUNTEXTENDEDTEXT & __TOTAL_ROWS,
                        __CURRENT_ROWS
                    ),
                    VAR __OVERAGE = __CURRENT_ROWS - __TOOMANY_MAX + 1
                    RETURN 
                        CONCATENATEX(
                            TOPN(__TOOMANY_MAX - 1,DISTINCT('Table'[Brand])),
                            [Brand],
                            __CONCATENATE_TEXT
                      ) & __TOOMANY_PRETEXT & __OVERAGE & __TOOMANY_POSTTEXT
                )
            )
        )
    )
VAR __PRETEXT = IF(ISFILTERED('Table'[Brand]),__DIRECTFILTERPRETEXT,__CROSSFILTERPRETEXT)
RETURN __PRETEXT & __MAINTEXT & __POSTTEXT

eyJrIjoiYzNhYWUyM2QtYzYwNi00MTJjLWJiMTMtYTY0OTdmMGM2MjBlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

jeffgarlisch — Wed, 30 Jan 2019 22:34:44 GMT

Hey there this quick measure is amazing, but it seems to interact oddly with my data set.

I have attached samples of my data, as well as images of what the error looks like!

I'm not so great at DAX myself, so i have yet to find the error.

Any help would be appreciated!

Thank you so much!

here is my changed code

Dynamic Slicer Title = 
VAR __ALLTEXT = "All"
VAR __NONETEXT = "No"
VAR __DIRECTFILTERPRETEXT = "You have chosen "
VAR __CROSSFILTERPRETEXT = "You have filtered down to "
VAR __POSTTEXT = " brand(s)."
VAR __CONCATENATE_TEXT = ", "
VAR __LASTCONCATENATE_TEXT = " and "
VAR __TOOMANY_MAX = 4
VAR __TOOMANY_PRETEXT = " and "
VAR __TOOMANY_POSTTEXT = " more"
VAR __USEPERCENT = FALSE() //Change to TRUE() to use percentages
VAR __PERCENTTEXT = " of"
VAR __PERCENTZEROTEXT = "0.00%"
VAR __USEMULTIPLECOUNT = FALSE() //Change to TRUE() to use counts after __TOOMANY_MAX is reached
VAR __USEMULTIPLECOUNTEXTENDED = FALSE() //Change to TRUE() to use "x of y" format after __TOOMANY_MAX is reached
VAR __USEMULTIPLECOUNTEXTENDEDTEXT = " of "
VAR __USESIMPLEMULTIPLETEXT = TRUE() //Change to TRUE() to use __SIMPLEMULTIPLETEXT after __TOOMANY_MAX is reached
VAR __SIMPLEMULTIPLETEXT = "Multiple" 
VAR __TOTAL_ROWS = COUNTROWS(DISTINCT(ALL(PLASFL[LastStatus])))
VAR __CURRENT_ROWS = COUNTROWS(DISTINCT(PLASFL[LastStatus]))
VAR __MAINTEXT = 
    IF(
        __USEPERCENT,
        VAR __PERCENT = DIVIDE(__CURRENT_ROWS,__TOTAL_ROWS,0)
        RETURN IF(ISBLANK(__PERCENT),__PERCENTZEROTEXT & __PERCENTTEXT,FORMAT(__PERCENT,"Percent") & __PERCENTTEXT),
        SWITCH(
            TRUE(),
            __CURRENT_ROWS = __TOTAL_ROWS,__ALLTEXT,
            __CURRENT_ROWS = 0,__NONETEXT,
            __CURRENT_ROWS = 1, MAX(PLASFL[LastStatus]),
            __CURRENT_ROWS < __TOOMANY_MAX,
                CONCATENATEX(
                    TOPN(__CURRENT_ROWS - 1,DISTINCT(PLASFL[LastStatus])),
                    PLASFL[LastStatus],
                    __CONCATENATE_TEXT
                ) & __LASTCONCATENATE_TEXT & LASTNONBLANK(DISTINCT(PLASFL[LastStatus]),TRUE()),
            IF(
                __USESIMPLEMULTIPLETEXT,
                __SIMPLEMULTIPLETEXT,
                IF(
                    __USEMULTIPLECOUNT,
                    IF(
                        __USEMULTIPLECOUNTEXTENDED,
                        __CURRENT_ROWS & __USEMULTIPLECOUNTEXTENDEDTEXT & __TOTAL_ROWS,
                        __CURRENT_ROWS
                    ),
                    VAR __OVERAGE = __CURRENT_ROWS - __TOOMANY_MAX + 1
                    RETURN 
                        CONCATENATEX(
                            TOPN(__TOOMANY_MAX - 1,DISTINCT(PLASFL[LastStatus])),
                            PLASFL[LastStatus],
                            __CONCATENATE_TEXT
                      ) & __TOOMANY_PRETEXT & __OVERAGE & __TOOMANY_POSTTEXT
                )
            )
        )
    )
VAR __PRETEXT = IF(ISFILTERED(PLASFL[LastStatus]),__DIRECTFILTERPRETEXT,__CROSSFILTERPRETEXT)
RETURN __PRETEXT & __MAINTEXT & __POSTTEXT

Re: The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

AlixB — Mon, 09 Aug 2021 13:55:30 GMT

Thanks, not sure I completely understand it as I'm pretty new to using DAX, but I've managed to tweak it to get it to work how I need it to. So it's great.

Thanks for your help

Re: The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

LoreG — Fri, 18 Aug 2023 10:12:56 GMT

Greg_Deckler This is truely amazing, thanks a lot for sharing!
I have one issue though related to the "ALLTEXT".
Easiest to illustrate with an example: let's say I have a table with 3 columns: Brands, Years, Sales amount.
I want to use your formula to retrieve the Brands, but I also have somewhere in the report a slicer for the Years.
If I don't sell each brand in each year, the Year slicer can cause that not all different brands are in my current selection, even though I don't actually filter on Brand. In that case, I would still like the text to display "ALL brands" via the ALLTEXT VAR. Because for the years in my selection, I DO have all brands. Any suggestion on how to obtain this?
Thanks a lot in advance!

Re: The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

Greg_Deckler — Fri, 18 Aug 2023 14:55:23 GMT

LoreG Perhaps try using ALLEXCEPT to keep the Brand filter but exclude any other filters like year.

Re: The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

LoreG — Mon, 21 Aug 2023 07:52:11 GMT

Thanks! Greg_Deckler
Unfortunately I'm then receiving the error 'visual has exceeded available resources' since it's quite a big dataset. 😞

Re: The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

LoreG — Mon, 21 Aug 2023 08:22:54 GMT

Greg_Deckler solved with ISFILTERED ! 🙂