DAX conditional format is not working with Blank/ No data cells!

[MohTawfik]

I have Matrix visual where I want to write a DAX formula to format cells based on two conditions, their values and status!

Expected Value	Status	Format Background color	Remarks
>0	Won	Green	Working fine
>0	Lost	Black	Working fine
>0	In process / Open / Stopped	Yellow	Working fine
=0		Red	Working fine
Blank / No Data		Red	not working

My problem is blank / no data cells are not formatted correct and doesn't return red color
and this is the formula I used to format!

ColorRule Opp_Background =


Var Opp_status =
SELECTEDVALUE(
    Opportunity[Lifecycle Status (Opportunity)]
)

Var Expected_Value =
sum(
    Opportunity[Expected Value]
)

Var Result_Opp =
SWITCH(
    True(),
    Expected_Value <= 0,
    "Red",
    isblank(Expected_Value),
    "Red",
    Expected_Value > 0 && Opp_status = "Lost" ,
    "Black",
    Expected_Value > 0 && or(Opp_status = "In Process",Opp_status = "Open") ,
    "Yellow",
    Expected_Value > 0 && Opp_status = "Stopped",
    "Yellow",
    "Green"
)

Return

    Result_Opp

  [  isblank(Expected_Value),

    "Red",]

   This part is not working !!!!!

[MohTawfik]

@v-jianboli-msft 
Thanks for the reply, but I got exactly the same result, still blank cells are not formated!!

[v-jianboli-msft]

Hi @MohTawfik ,

What does your original data look like?

Please check what exactly are these blank values?

Here are some decuments about this, hope they could help you:

How to handle BLANK in DAX measures - SQLBI

Handling BLANK in DAX - SQLBI

Best Regards,

Jianbo Li

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

[MohTawfik]

Thanks for your continues support, I came across these two topics but still stuck.

That's a sample file with my data and formula to check

Conditional Format.pbix 

[v-jianboli-msft]

Hi @MohTawfik ,

With your PBIX file I found the problem:
The conditional formatting doesn't work because the blank cells you see don't actually exist, they are created by the intersection of rows and columns. Using blank() doesn't work because they are not values in any real sense.

So if you want them to be conditionally formatted the way you want, the easiest way is to change the default color of the cells in Visual to red, and then conditionally format the other values:

Final output:

Best Regards,

Jianbo Li

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

[MohTawfik]

Thanks again, I actually did this solution however it's not very practical as I have different levels of heirarcy where I don't want to keep the same format!
it's a bit complex one ! 🥴

[v-jianboli-msft]

Hi @MohTawfik ,

Please try:

SWITCH(
    True(),
    Expected_Value <= 0,
    "Red",
    Expected_Value = BLANK(),
    "Red",
    Expected_Value > 0 && Opp_status = "Lost" ,
    "Black",
    Expected_Value > 0 && or(Opp_status = "In Process",Opp_status = "Open") ,
    "Yellow",
    Expected_Value > 0 && Opp_status = "Stopped",
    "Yellow",
    "Green"
)

Return

    Result_Opp

Best Regards,

Jianbo Li

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