Help to write DAX command for the color formatting conditions

[dinesharivalaga]

Hi Experts,

I am working on the below condition to create a calculated column with the color combinations for each outputs from source and converted into conditional formatting.

Logic :

if "Do we have required resourcing for the project?"is no, then Amber

if "Are there attritions/Backfill delayed?" is Yes, then Amber

if "Are we having any skill issues in the team?" if Yes, then Red

if any one of them is red above, then Red, if none of them red and any one of them amber then Amber

All the above questions are already available in sharepoint list and end users can fill the input as Yes or No later.

I have written the DAX as below but in that i have mentioned "Green" color for else conditions , but instead of "Green" we have to use last line as a logic , so am struggling on that .

Resourcing logic = IF('Delivery Updates'[Dowehaverequiredresourcingforthe]="No","Amber",IF('Delivery Updates'[Arethereattritions/Backfil]="Yes","Amber",IF('Delivery Updates'[Arewehavinganyskillissuesinthete]="Yes","Red","Green")))

So far there is no input from end users , so the result is coming as "Green" only.

Please help to achieve this for me 🙂

Thanks

DK

[Anonymous]

Hi  @dinesharivalaga ,

Not quite sure of the logic, I see multiple identical situations displayed twice in your description, but in different colors.

Are you referring to the following: 

In “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” scenario, “In your description you see multiple identical situations displayed twice, but in different colors. “Yes and “Is the CR approved?” is No, it stays Red

In  “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“ scenario, ‘If ’Is a CR proposed?” is yes and “Is a CR approved?” is no, then keep red amber color “.

You can try the following calculated columns

Column =
SWITCH(
    TRUE(),
    [Are there any changes between SOW Agreed Scope]="Yes" && [Is the devaition between SOW Agreed scope and revised scope has major deviation]="Yes" && [istheCRraised?]="Yes"&&[Is the CR approved]="No","Red",
    [Are there any changes between SOW Agreed Scope]="Yes" && [Is the devaition between SOW Agreed scope and revised scope has major deviation]="No" && [istheCRraised?]="Yes"&&[Is the CR approved]="No","Amber",
    [istheCRraised?]="Yes"&&[Is the CR approved]="Yes","Green",BLANK())

Best Regards,

Liu Yang

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

[dinesharivalaga]

@Anonymous  Thanks for your support 🙂

It works for few matches and not for all Lui Yang .

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

Actually let me reclarify the scenario , if the RED line is matched then it will not go to GREEN line case and vice versa.

RED batch :
Sometimes the end users will update only 1st 2 questions as "YES" and keep blank with next 2 , in that case the output is "RED" only . here in your formula it seems we have to fill all the 4 questions , otherwise it is giving wrong output.

For example : (4 inputs)

Output :

 this output is correct when 4 inputs are there.

If we remove last 2 inputs , then it is given wrong output as below:

Output :

 actual output should be "Amber" , but it is giving BLANK.

I hope now it is understandable ..

Thanks

[Anonymous]

Thanks for the reply from dharmendars007 , please allow me to provide another insight:
Hi  @dinesharivalaga ,

Here are the steps you can follow：

1. Create calculated column.

Resourcing logic =
var _if1=
IF('Table'[Dowehaverequiredresourcingforthe]="No","Amber",BLANK())
var _if2=
IF('Table'[Arethereattritions/Backfil]="Yes","Amber",BLANK())
var _if3=
IF('Table'[Arewehavinganyskillissuesinthete]="Yes","Red",BLANK())
var _text=
_if1&"-"&_if2&"-"&_if3
return
IF(
    CONTAINSSTRING(_text,"Red")=TRUE(),"Red","Amber")

2. Result:

Best Regards,

Liu Yang

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

[dinesharivalaga]

@Anonymous  Hi Yang ,

In the above solution , it is fine when all the questions are answered (Yes or No) , but some accounts are not updated yet and that also showing as "Amber".

For example :

Now all the 3 fields are blank now for all the accounts , in that case it should give BLANK values in tha table too but it is showing "Amber" as below :

Requirement is "if all the fields are blank then respective account resourcing column also should be blank , if any one of them is answered others are blank then based on the answered one the column should reflect" .

Please help to include that conditions on this ?

Thanks

DK

[dinesharivalaga]

@Anonymous Thanks a lot , it will work once end users are updating their input for those questions.

Also like this same i was struggling to get one more logic conditions as below :

Scope column 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 and then i can convert those colors into icons by conditional formatting like below .

I have tried this below DAX but not sure whether it will work or not.

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 try to check and validate my DAX ?..

[dinesharivalaga]

@Anonymous  Hi , Can you help on the above one also ?

Thanks ..

[dharmendars007]

Hello @dinesharivalaga , 

when you say "Last line" can you please specify what you want to include in else statement based on that you can replace the Green condition..

Resourcing logic = IF('Delivery Updates'[Dowehaverequiredresourcingforthe]="No","Amber",IF('Delivery Updates'[Arethereattritions/Backfil]="Yes","Amber",IF('Delivery Updates'[Arewehavinganyskillissuesinthete]="Yes","Red","Last line")))

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[dinesharivalaga]

@dharmendars007  Last line means , last line from the logic ..

Logic :

if "Do we have required resourcing for the project?"is no, then Amber

if "Are there attritions/Backfill delayed?" is Yes, then Amber

if "Are we having any skill issues in the team?" if Yes, then Red

if any one of them is red above, then Red, if none of them red and any one of them amber then Amber

instead of "Green" in my DAX i have to replace with the last line else condition 🙂