topic Re: Write DAX on multiple IF condition with the color formatting column in DAX Commands and Tips

Write DAX on multiple IF condition with the color formatting column

Anonymous — Mon, 09 Sep 2024 05:28:24 GMT

Hi Experts,

I am working and struggling to get one of the IF logic conditions as below :

Logic :

if "Are there any changes between SOW Agreed Scope & Revised Scope?" is Yes and "Is the devaition between SOW Agreed scope and revised scope has major deviation?" is Yes --> Red

if "Is the CR raised?" is yes or No then keep it Red

if "Is the CR raised?" is yes and "Is the CR approved?" is No then keep it Red

if "Is the CR raised?" is yes and "Is the CR approved?" is yes then change it to Green

if "Are there any changes between SOW Agreed Scope & Revised Scope?" is Yes and "Is the devaition between SOW Agreed scope and revised scope has major deviation?" is No --> Amber

if "Is the CR raised?" is yes or No then keep it Amber

if "Is the CR raised?" is yes and "Is the CR approved?" is No then keep it Amber

if "Is the CR raised?" is yes and "Is the CR approved?" is yes then change it to Green

All the above questions are already available in the Sharepoint list source , based on the above logics I have to create a column for the accounts like below .

I have tried this below DAX but not working as expected.

Scope logic=

IF (

[ArethereanychangesbetweenSOWAgre] = "Yes" &&

[IsthedevaitionbetweenSOWAgreedsc] = "Yes",

"Red",

IF (

[istheCRraised?] = "Yes" &&

[IstheCRapproved?] = "No",

"Red",

IF (

[ArethereanychangesbetweenSOWAgre] = "Yes" &&

[IsthedevaitionbetweenSOWAgreedsc] = "No",

"Amber",

IF (

[istheCRraised?] = "Yes" &&

[IstheCRapproved?] = "No",

"Amber",

"Green"

)

)))

Please help to solve this DAX ?..

Thanks

Re: Write DAX on multiple IF condition with the color formatting column

ValtteriN — Mon, 09 Sep 2024 05:38:30 GMT

Hi,

Instead of using nested IFs you can use SWITCH + TRUE:

ScopeLogic =
SWITCH(TRUE(),
[ArethereanychangesbetweenSOWAgre] = "Yes" && [IsthedevaitionbetweenSOWAgreedsc] = "Yes", "Red",
[istheCRraised?] = "Yes" && [IstheCRapproved?] = "No", "Red",
[ArethereanychangesbetweenSOWAgre] = "Yes" && [IsthedevaitionbetweenSOWAgreedsc] = "No", "Amber",
[istheCRraised?] = "Yes" && [IstheCRapproved?] = "No", "Amber",
"Green"
)

With this approach if some of the rows is not working as expected it is easier to troubleshoot and add new options.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Write DAX on multiple IF condition with the color formatting column

Anonymous — Mon, 09 Sep 2024 06:07:40 GMT

ValtteriN Thanks for your solution.

if "Is the CR raised?" is yes and "Is the CR approved?" is yes then change it to Green

this above lines are not included in both the 2 conditions , is it works still ?

Re: Write DAX on multiple IF condition with the color formatting column

abuislam — Mon, 09 Sep 2024 08:10:07 GMT

Your DAX formula needs to account for all conditions and their priorities. Here’s a revised version:

DAX

Copy code

Scope logic = SWITCH ( TRUE (), [ArethereanychangesbetweenSOWAgre] = "Yes" && [IsthedevaitionbetweenSOWAgreedsc] = "Yes", "Red", [istheCRraised?] = "Yes" && [IstheCRapproved?] = "No", "Red", [ArethereanychangesbetweenSOWAgreedsc] = "Yes" && [IsthedevaitionbetweenSOWAgreedsc] = "No", "Amber", [istheCRraised?] = "Yes" && [IstheCRapproved?] = "Yes", "Green", "Amber" )

This approach uses SWITCH with TRUE to handle multiple conditions more clearly. Adjust as needed based on your exact requirements.

Re: Write DAX on multiple IF condition with the color formatting column

ValtteriN — Mon, 09 Sep 2024 11:44:51 GMT

Hi Anonymous ,

In the formula I provided green is fallback color so if other checks don't have a match the color is green.
ScopeLogic =
SWITCH(TRUE(),
[ArethereanychangesbetweenSOWAgre] = "Yes" && [IsthedevaitionbetweenSOWAgreedsc] = "Yes", "Red",
[istheCRraised?] = "Yes" && [IstheCRapproved?] = "No", "Red",
[ArethereanychangesbetweenSOWAgre] = "Yes" && [IsthedevaitionbetweenSOWAgreedsc] = "No", "Amber",
[istheCRraised?] = "Yes" && [IstheCRapproved?] = "No", "Amber",
"Green"
)

Re: Write DAX on multiple IF condition with the color formatting column

Anonymous — Fri, 04 Oct 2024 08:48:35 GMT

Hi Anonymous

Did ValtteriN 's and abuislam 's methods solve your problem? If yes, could you please mark them as solutions? This will help more users who are facing the same or similar difficulties. Thank you!

If you still have questions, please feel free to ask me.

Best Regards,
Yulia Xu

Re: Write DAX on multiple IF condition with the color formatting column

Anonymous — Fri, 04 Oct 2024 08:59:14 GMT

Hi Anonymous Yes will mark it .
Currently i am stuck with the following query which is also posted in the community.
https://community.fabric.microsoft.com/t5/Desktop/Cannot-add-measure-as-legend-in-pie-chart/m-p/4214858#M1324538

Please help on this ..