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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.