Write DAX on multiple IF condition with the color formatting column

[dinesharivalaga]

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

DK

[Anonymous]

Hi @dinesharivalaga 

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

[dinesharivalaga]

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 ..

[abuislam · Accepted solution]

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.

[ValtteriN]

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/

[dinesharivalaga]

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

[ValtteriN]

Hi @dinesharivalaga ,

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