skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • The Most Amazing, Mind Blowing Dynamic Slicer Titl...

    The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

    05-31-2018 08:56 AM - last edited 06-01-2018 05:16 AM

    Super User Greg_Deckler
    Super User
    27453 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    The Most Amazing, Mind Blowing Dynamic Slicer Title Measure Ever

    ‎05-31-2018 08:56 AM

    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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Preview file
    54 KB
    DynamicSlicerTitle.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 7
    27,453 Views
    13
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    LoreG
    LoreG
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-18-2023 02:18 AM

    @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!

    Message 4 of 7
    562 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to LoreG
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-18-2023 07:55 AM

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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 5 of 7
    542 Views
    0
    Reply
    LoreG
    LoreG
    Regular Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-21-2023 12:52 AM

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

    Message 6 of 7
    512 Views
    0
    Reply
    LoreG
    LoreG
    Regular Visitor
    In response to LoreG
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-21-2023 01:22 AM

    @Greg_Deckler  solved with ISFILTERED ! 🙂

    Message 7 of 7
    509 Views
    2
    Reply
    AlixB
    AlixB
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-09-2021 06:55 AM

    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

    Message 3 of 7
    9,118 Views
    0
    Reply
    jeffgarlisch
    jeffgarlisch Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-30-2019 02:32 PM

    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
    Preview file
    34 KB
    Preview file
    32 KB
    Preview file
    519 KB
    Message 2 of 7
    24,937 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices