Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
DHB
Helper IV
Helper IV

Please help me to fix my DAX

I would like to use DAX to insert a header which shows what filter selections have been made.  It need to show "Level (Group): " followed by the filter selection.  If there's more than one selection then to show them all separated by ", " and if there are no filter selections then to just show the following text, "Level (Group): All".

 

My DAX is...

 

Commencing Header =
IF (
    ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
    "Level (Group): "
        & CONCATENATE (
            ALLSELECTED ( 'Austrade (Append)'[Level (groups)] ),
            "Level (Group): All"
        )
)
 
This is what it gives me though.
DHB_0-1678843508759.png

 

Any tips would be gratefully received.

 

Best,

 

DHB

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

@DHB 

Give this a try

Commencing Header = 
IF (
    ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
    VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
    VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
    VAR __x = "Level (Group): " & __d
    RETURN __x,
    "Level (Group): All"
)

Taken from this article: https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/

 

View solution in original post

jdbuchanan71
Super User
Super User

Yes, we can add a count of the total number of items and compare the selection to that.

Commencing Header = 
IF (
    ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
    VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
    VAR __t = COUNTROWS ( CALCULATETABLE ( DISTINCT ( 'Austrade (Append)'[Level (groups)] ), ALL ( 'Austrade (Append)' ) ) )
    VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
    VAR __x = IF ( COUNTROWS ( __f ) = __t, "Level (Group): All", "Level (Group): " & __d)
    RETURN __x,
    "Level (Group): All"
)

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User
Super User

Yes, we can add a count of the total number of items and compare the selection to that.

Commencing Header = 
IF (
    ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
    VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
    VAR __t = COUNTROWS ( CALCULATETABLE ( DISTINCT ( 'Austrade (Append)'[Level (groups)] ), ALL ( 'Austrade (Append)' ) ) )
    VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
    VAR __x = IF ( COUNTROWS ( __f ) = __t, "Level (Group): All", "Level (Group): " & __d)
    RETURN __x,
    "Level (Group): All"
)

Thank you @jdbuchanan71 that is totally awesome.

jdbuchanan71
Super User
Super User

@DHB 

Give this a try

Commencing Header = 
IF (
    ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
    VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
    VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
    VAR __x = "Level (Group): " & __d
    RETURN __x,
    "Level (Group): All"
)

Taken from this article: https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/

 

That's perfect, thank you @jdbuchanan71 .  If someone manually clicked all 5 to get all, rather than not selecting any, is there something I could add to that code to make it read "Level (Group): All" instead of getting a concatented list of all 5?

BrianConnelly
Resolver III
Resolver III

Check if you only have one value and then return that one value, if more than one value use the following.

CONCATENATEX('Austrade(Append)', 'Austrade(Append)'[Level (Groups)], ",") 
Ahmedx
Super User
Super User

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.