SWITCH conditions are not working as expected

[dinesharivalaga]

Hi Experts,

I am using SWITCH dax for my scenario and it has multiple conditions.

few conditions are matched and working fine and result is expected. but few conditions are not reflecting the result even the input is entered.

Below the requirements :

#1 if "Have we met planned scope YTD with Delivered scope so far?" is "Yes" then Green
#2 if "Have we met planned scope YTD with Delivered scope so far?" is "No" and "Is there a catch up plan to meet the target?" is "Yes" then Amber, else Red
#3 if "Is the delay caused by customer side or from CG side?" is "Customer" then change the color to Amber even #2 is Red
#4 if the deviation of "YTD Effort Spent" is more than 10% of "YTD Effort Planned", it should be Red, if less than 10%, it is amber otherwise Green
#5 if the "Total Effort in PD" is more than "YTD Effort Spent"+Effort still needed to complete the project", then green, if deviation is less than 10% then Amber, if it is more than 10% then it is RED
First 4 points are working fine but 5th is not working , i don't know if it is related to DAX issue or SWITCH function issue.

Calculated column DAX formula

Schedule logic = SWITCH(TRUE(),

'Test Delivery Updates'[Have we met planned scope YTD with Delivered scope so far?]="Yes","Green",

'Test Delivery Updates'[Have we met planned scope YTD with Delivered scope so far?]="No" && 'Test Delivery Updates'[Is there a catch up plan to meet the target?]="Yes","Amber",

'Test Delivery Updates'[Is the delay caused by customer side or from CG side?]="Customer","Amber",

'Test Delivery Updates'[Is the delay caused by customer side or from CG side?]="CG","Red",

('Test Delivery Updates'[YTD Effort Spent])>('Test Delivery Updates'[YTD Effort Planned]+'Test Delivery Updates'[YTD Effort Planned]*0.1),"Red",

('Test Delivery Updates'[YTD Effort Spent])<('Test Delivery Updates'[YTD Effort Planned]-'Test Delivery Updates'[YTD Effort Planned]*0.1),"Amber",

('Test Delivery Updates'[YTD Effort Spent])=('Test Delivery Updates'[YTD Effort Planned]),"Green",

('Test Delivery Updates'[Total Effort in PD])>('Test Delivery Updates'[YTD Effort Spent]+'Test Delivery Updates'[Effort still needed to complete the project]),"Green",

ABS(('Test Delivery Updates'[Total Effort in PD])-('Test Delivery Updates'[YTD Effort Spent]+'Test Delivery Updates'[Effort still needed to complete the project]))<('Test Delivery Updates'[Total Effort in PD]*0.1),"Amber",

ABS(('Test Delivery Updates'[Total Effort in PD])-('Test Delivery Updates'[YTD Effort Spent]+'Test Delivery Updates'[Effort still needed to complete the project]))>('Test Delivery Updates'[Total Effort in PD]*0.1),"Red"

)

Last 3 rows is not working as expected (5th requirement point)

Expected result is "Green , Red, Amber" but it is Showing RED for all the 3 rows.

Please help to suggest some ideas on this.

Thanks

DK

[HotChilli]

Right, so you have a situation where either:

a) The logic conditions are in the wrong order,

or

b) The logic conditions are not compatible with each other (it doesn't matter what order you put them in, you will still get wrong results in some rows)

--

You might try drawing up a flow chart to see if you can get the logic right before writing the DAX for it.

[HotChilli]

The order of conditions is vital here. Are these 'RED' rows meeting a condition before the one you think it should be meeting?

[dinesharivalaga]

@HotChilli Yes i have updated the input based on the conditions .

Now here you can see after rearranging the condition orders :

Last 3 rows are giving exact result and all RED comes for previous 3 rows.

Rearranged DAX conditions :

Schedule logic =SWITCH(TRUE(),

('Test Delivery Updates'[Total Effort in PD])>('Test Delivery Updates'[YTD Effort Spent]+'Test Delivery Updates'[Effort still needed to complete the project]),"Green",

ABS(('Test Delivery Updates'[Total Effort in PD])-('Test Delivery Updates'[YTD Effort Spent]+'Test Delivery Updates'[Effort still needed to complete the project]))<('Test Delivery Updates'[Total Effort in PD]*0.1),"Amber",

ABS(('Test Delivery Updates'[Total Effort in PD])-('Test Delivery Updates'[YTD Effort Spent]+'Test Delivery Updates'[Effort still needed to complete the project]))>('Test Delivery Updates'[Total Effort in PD]*0.1),"Red",

'Test Delivery Updates'[Have we met planned scope YTD with Delivered scope so far?]="Yes","Green",

'Test Delivery Updates'[Have we met planned scope YTD with Delivered scope so far?]="No" && 'Test Delivery Updates'[Is there a catch up plan to meet the target?]="Yes","Amber",

'Test Delivery Updates'[Is the delay caused by customer side or from CG side?]="Customer","Amber",

'Test Delivery Updates'[Is the delay caused by customer side or from CG side?]="CG","Red",

'Test Delivery Updates'[YTD Effort Spent]>'Test Delivery Updates'[YTD Effort Planned]+'Test Delivery Updates'[YTD Effort Planned]*0.1,"Red",

'Test Delivery Updates'[YTD Effort Spent]<'Test Delivery Updates'[YTD Effort Planned]-'Test Delivery Updates'[YTD Effort Planned]*0.1,"Amber",

'Test Delivery Updates'[YTD Effort Spent]='Test Delivery Updates'[YTD Effort Planned],"Green"

)

[v-rzhou-msft · Accepted solution]

Hi @dinesharivalaga ,

According to your statement, I think you can try code as below.

Schedule logic = 
IF (
    'Test Delivery Updates'[YTD Effort Planned] + 'Test Delivery Updates'[YTD Effort Spent] > 0
        && 'Test Delivery Updates'[YTD Effort Planned] * 'Test Delivery Updates'[YTD Effort Spent] <> 0,
    SWITCH (
        TRUE (),
        ( 'Test Delivery Updates'[YTD Effort Spent] ) > ( 'Test Delivery Updates'[YTD Effort Planned] + 'Test Delivery Updates'[YTD Effort Planned] * 0.1 ), "Red",
        ( 'Test Delivery Updates'[YTD Effort Spent] ) < ( 'Test Delivery Updates'[YTD Effort Planned] - 'Test Delivery Updates'[YTD Effort Planned] * 0.1 ), "Amber",
        ( 'Test Delivery Updates'[YTD Effort Spent] ) = ( 'Test Delivery Updates'[YTD Effort Planned] ), "Green"
    ),
    SWITCH (
        TRUE (),
        'Test Delivery Updates'[Have we met planned scope YTD with Delivered scope so far?] = "Yes", "Green",
        'Test Delivery Updates'[Have we met planned scope YTD with Delivered scope so far?] = "No"
            && 'Test Delivery Updates'[Is there a catch up plan to meet the target?] = "Yes", "Amber",
        'Test Delivery Updates'[Is the delay caused by customer side or from CG side?] = "Customer", "Amber",
        'Test Delivery Updates'[Is the delay caused by customer side or from CG side?] = "CG", "Red",
        ( 'Test Delivery Updates'[Total Effort in PD] ) > ( 'Test Delivery Updates'[YTD Effort Spent] + 'Test Delivery Updates'[Effort still needed to complete the project] ), "Green",
        ABS ( ( 'Test Delivery Updates'[Total Effort in PD] ) - ( 'Test Delivery Updates'[YTD Effort Spent] + 'Test Delivery Updates'[Effort still needed to complete the project] ) ) < ( 'Test Delivery Updates'[Total Effort in PD] * 0.1 ), "Amber",
        ABS ( ( 'Test Delivery Updates'[Total Effort in PD] ) - ( 'Test Delivery Updates'[YTD Effort Spent] + 'Test Delivery Updates'[Effort still needed to complete the project] ) ) > ( 'Test Delivery Updates'[Total Effort in PD] * 0.1 ), "Red"
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[dinesharivalaga]

@v-rzhou-msft  Thanks a lot for this solution 🙂

It works now and will change the input and check for the output .