Measure Conditional formatting

[AllanBerces]

Hi good day, can anyone help me to correct my measure  on  contional formatting. I need if the Ratio is below target then Red if above then green and Orange if same.

Mismatch Color_rev = SWITCH(

    TRUE(),

    [Mech_Ratio] && [Scaff_Ratio] && [El_Ratio] > [Mech Target] && [Scaff Target] && [EI Target] , "RED",

    [Mech_Ratio] && [Scaff_Ratio] && [El_Ratio] < [Mech Target] && [Scaff Target] && [EI Target] , "GREEN",

    "ORANGE")

Thank you

[danextian · Accepted solution]

Hi @AllanBerces 

You formula is invalid because of how logical conditions are written. Each condition must be fully specified but you took a shortcut isntead.  [Mech_Ratio] && [Scaff_Ratio] && [El_Ratio] - will return either true or false and you compare that with the second part of the condition which will return either true or false as well. True/False > True/False just doesnt make sense in this context. Try:

Mismatch Color_rev =
SWITCH (
    TRUE (),
    [Mech_Ratio] > [Mech Target]
        && [Scaff_Ratio] > [Scaff Target]
        && [El_Ratio] > [EI Target], "RED",
    [Mech_Ratio] < [Mech Target]
        && [Scaff_Ratio] < [Scaff Target]
        && [El_Ratio] < [EI Target], "GREEN",
    "ORANGE"
)

[V-yubandi-msft · Accepted solution]

Hi @AllanBerces ,

Thank you for bringing this up, and I appreciate @danextian , @ryan_mayu  valuable input on the logic structure. I’ve tested the full scenario and can confirm that the measure works as intended.
FYI:

I used Field value formatting in Power BI, and the colors display correctly. If you notice gradients or blue tones, please check that the format style is set to Field value, not Gradient.

I’ve attached a .pbix file with sample data for your reference. Let me know if you want to adjust the logic or make it dynamic.

Regards,

Yugandhar.

[danextian · Accepted solution]

Hi @AllanBerces 

You formula is invalid because of how logical conditions are written. Each condition must be fully specified but you took a shortcut isntead.  [Mech_Ratio] && [Scaff_Ratio] && [El_Ratio] - will return either true or false and you compare that with the second part of the condition which will return either true or false as well. True/False > True/False just doesnt make sense in this context. Try:

Mismatch Color_rev =
SWITCH (
    TRUE (),
    [Mech_Ratio] > [Mech Target]
        && [Scaff_Ratio] > [Scaff Target]
        && [El_Ratio] > [EI Target], "RED",
    [Mech_Ratio] < [Mech Target]
        && [Scaff_Ratio] < [Scaff Target]
        && [El_Ratio] < [EI Target], "GREEN",
    "ORANGE"
)

[AllanBerces]

Hi @danextian @ryan_mayu @V-yubandi-msft thank you very much for the reply work asi need.

[ryan_mayu]

@AllanBerces 

maybe you can try this. Not sure if I understand your logic clearly, if this does not work ,you can try to change && to || to have a try.

Mismatch Color_rev =
SWITCH(
TRUE(),
[Mech_Ratio] < [Mech Target] && [Scaff_Ratio] < [Scaff Target] |&&[El_Ratio] < [EI Target], "RED",
[Mech_Ratio] > [Mech Target] && [Scaff_Ratio] > [Scaff Target] && [El_Ratio] > [EI Target], "GREEN",
[Mech_Ratio] = [Mech Target] && [Scaff_Ratio] = [Scaff Target] && [El_Ratio] = [EI Target], "ORANGE",
"ORANGE" 
)