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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors