Conditional Formatting in Table Visual when the data is missing

[AnkitaaMishra]

Hi All, 
I am facing a challenge with conditional formatting when the row is not present in the table.
Table : 

ID	Date	Parameter	VALUE	AC_Target	DM_Target
20290	03-07-2021 00:00	DM	88.7	Off Target	On Target
20290	03-07-2021 00:00	AC	98.8	On Target	Off Target
21769	05-07-2022 00:00	DM	87.2	Off Target	On Target
21769	05-07-2022 00:00	AC	100	On Target	Off Target
21779	08-07-2022 00:00	DM	76.3	Off Target	Off Target
21779	08-07-2022 00:00	AC	100	On Target	Off Target
22503	08-01-2023 00:00	DM	90.4	Off Target	On Target
22503	08-01-2023 00:00	AC	94	On Target	Off Target
24042	12-08-2023 00:00	AC	98	On Target	Off Target
26278	10-09-2024 00:00	AC	100	On Target	Off Target

In the above table, AC_Target and DM_Target are the calculated columns with below DAX :

DM_Target = IF(ExampleProblem[Parameter]="DM" && ExampleProblem[VALUE]>=85, "On Target", "Off Target")

AC_Target = IF(ExampleProblem[Parameter]="AC" && ExampleProblem[VALUE]>=90, "On Target", "Off Target")

For conditional formatting I am using below Measure : 

ConditionFormatting =

VAR _Target = MAX(ExampleProblem[DM_Target])

VAR _ColorLogic =

SWITCH(TRUE(),

_Target="On Target", "#4BA02C",

_Target="Off Target", "#DD5051","#FFFFFF")

RETURN

_ColorLogic

Now below is the output : 

What I want to do is wherever it is coming blank I need White background instead of Red from above screenshot. 
Logic wise actually where the blank is coming those rows are not present in source, hence its tagging as Off Target and then coming Red.

Request for your help here.

Thanks, 
Ankita

[AnkitaaMishra]

Thanks for quick response @Uzi2019 , I had multiple AND conditions for the Target column but implemented the same logic suggested by you for both >= and < and it worked perfectly. 🙂

[Uzi2019 · Accepted solution]

Hi @AnkitaaMishra 

You can modify your condition for DM and AC target

DM_Target2 = IF('Table target'[Parameter]="DM" && 'Table target'[VALUE]>=85, "On Target", if('Table target'[Parameter]="DM" && 'Table target'[VALUE]<85,"Off Target", BLANK()))

I hope this is what you are looking for.

I hope I answered your question!

[Uzi2019]

Hi @AnkitaaMishra 

create similar coumn for AC target.

your conditional formatting logic was correct just that you calculated column for DM and AC target missing one condition for blank(). that why showing off target where condition is not met.

I hope you understood my point.

I hope above solution would help you to understand and solve your issue!