The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

Super User
2035 Views
Highlighted
Super User
Posts: 9,535
Registered: ‎07-11-2015

The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

[ Edited ]

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

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment