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
kalkhudary
Helper III
Helper III

Dax formula for matching string values between different columns for conditional formatting purposes

Hello Community, 

 

I am trying to dax the following logic to be able to use it as a conditional format on a matrix table " Project Column" under the below logic. So the logic says if the project mentioned is tagged to any of the projects subtypes give me a 0 and if not than 1.

 

Here we have 3 projects with their assigned subtypes and no subtype of another project should be tagged to any to other project.

 

WhenI created the below dax, it looks correct but it is not working and giving me an error when I try to set it to the field "projects" as conditional format so not sure if it is working or even daxed right.

 

Any help and guidance would be Super!

 

Improper Program Tagging - CF=

MAXX('Table1',

IF('Table1' [Project]= "Creativity" && 'Table1' [Project Subtype] IN {"ABCD","Crafts","CDO", "PR", "MF 6"}, 0,1) ||

IF('Table1' [Project]= Toys" && 'Table1' [Project Subtype] = “Lego",0,1) ||

IF('Table1' [Project]="Online Gaming" && 'Table1' [Project Subtype]= "Avengers",0,1))

1 ACCEPTED SOLUTION

Hi , @kalkhudary 

According to your dax code , you try to return 1/0 according to the different situation. For your need , you can try to use this dax code:

SWITCH(TRUE(),
MAX('Table1' [Project])= "Creativity" && MAX('Table1' [Project Subtype]) in {"ABCD","Crafts","CDO", "PR", "MF 6"} , 0,
MAX('Table1' [Project])= "Toys" && MAX('Table1' [Project Subtype]) = "Lego" , 0 ,
MAX('Table1' [Project])= "Online Gaming" && MAX('Table1' [Project Subtype]) = "Avengers",0,1
)

 

As the differnt data structure and differnt content filter may need to use differnt dax code . And i have no sample data . If this dax code can not meet your need , you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

6 REPLIES 6
kalkhudary
Helper III
Helper III

Thanks Aniya for your answer. It helps put my thoughts together in shape. Much appreciated.

kalkhudary
Helper III
Helper III

@lbendlin I didn't actually. Would you have a better suggestion or way that can match the above logic.

Hi , @kalkhudary 

According to your dax code , you try to return 1/0 according to the different situation. For your need , you can try to use this dax code:

SWITCH(TRUE(),
MAX('Table1' [Project])= "Creativity" && MAX('Table1' [Project Subtype]) in {"ABCD","Crafts","CDO", "PR", "MF 6"} , 0,
MAX('Table1' [Project])= "Toys" && MAX('Table1' [Project Subtype]) = "Lego" , 0 ,
MAX('Table1' [Project])= "Online Gaming" && MAX('Table1' [Project Subtype]) = "Avengers",0,1
)

 

As the differnt data structure and differnt content filter may need to use differnt dax code . And i have no sample data . If this dax code can not meet your need , you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

That worked perfectly! Thanks for your help really. Would you be able to explain to me why this worked better than the code above just for me to learn the difference and understand how you thought about it.

Hi , @kalkhudary 

The MAX() function in the visual is return the current context filter value  , in the visual we just need to use the MAX() function to get the cuurent value like Projuct and subType.

You can create a simple measure like :
Measure =  MAX('Table'[Project])

then you can  put it on your Matrix visual to test what the value return so you can understand it!~

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

lbendlin
Super User
Super User

That's a rather inventive way of applying filters.  Have you tried the standard way?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.