Please help me to fix my DAX

[DHB]

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.

Any tips would be gratefully received.

Best,

DHB

[jdbuchanan71 · Accepted solution]

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

[jdbuchanan71 · Accepted solution]

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"
)

[DHB]

Thank you @jdbuchanan71 that is totally awesome.

[jdbuchanan71 · Accepted solution]

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

[DHB]

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]

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]

watch this video
https://www.youtube.com/watch?v=iZM96Ie4Dpg&ab_channel=EnterpriseDNA