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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DHB
Helper V
Helper V

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.