Conditional Formatting on Blank Matrix Cells

[Anonymous]

Hello, please help me out I am new to DAX! So, I am trying to apply conditional formatting to this sample table such that the blank cells are treated as zero, and also to remove the formatting from the first column. This is essentially weekly quantity of a different products, with products on x axis and weeks on y axis. The idea is to highlight the cell if the value is more (green) or less (pink) than the previous week. I created this measure and applied as field value in conditional formatting.

format =

SWITCH (

TRUE (),

SUM(Append1[PDQ] )< CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)), "pink",

SUM(Append1[PDQ] )> CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)),"lightgreen")

Please advise. I have tried too many things

[v-janeyg-msft]

Hi, @Anonymous 

There are several ways of conditional format, which can be used flexibly according to needs. Can you share some sample fake data or more information about the value? Is the value SUM(Append1[PDQ] )? Is it a column or a measure? 

I want to help you but i'm a little confused.

Reference:

Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs

If you still need help, please feel free to ask me.

Best Regards

Janey Guo

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

[Anonymous]

Hi @v-janeyg-msft , thanks so much for your response! Can I send you a sample pbix file in message? If you can send me a message I can reply to it with that for some reason am unable to send one myself.

[v-janeyg-msft]

Hi, @Anonymous  

I'm sorry. Due to the nature of work, our communication is limited to post replies. 

You need to give me the structure of the data you use in the matrix, the code of the value and your final results like , You can also send it in the form of pictures and forms and attention to erase private information. I will try my best to help you. Of course, if you can send out a sample file, it might be faster.

Best Regards

Janey Guo

[Anonymous]

@v-janeyg-msft Its not letting me post for some reason. I am getting an error that post flooding detected.

[v-janeyg-msft]

Hi, @Anonymous 

If there's a problem, You can also share your sample data information through these methods.

Although it's a bit troublesome, it's hard to solve your problem at once if you don't provide specific information(I asked you before).

Best Regards

Janey Guo

[amitchandak]

@Anonymous , Based on what I got. Try a measure like

format =
SWITCH (
TRUE (),
isblank(SUM(Append1[PDQ] )) && isblank((SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY))) ,"red",
SUM(Append1[PDQ] )< CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)), "pink",
SUM(Append1[PDQ] )> CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)),"lightgreen")

[Anonymous]

Thank you for your prompt response! I tried this but its not working (I think you meant to add CALCULATE as well so I edited that in):

format =

SWITCH (

TRUE (),

isblank(SUM(Append1[PDQ])) && isblank((CALCULATE(SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)))),"red",

SUM(Append1[PDQ] )< CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)), "pink",

SUM(Append1[PDQ] )> CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)),"lightgreen")

Same result

[amitchandak]

@Anonymous , ok got, if the value is blank, conditional formatting will not work

change you measure with +0 (which you have used on values and then add condition for that [measure]=0 in the color

Asumme you are displaying SUM(Append1[PDQ])

use measure 1 =SUM(Append1[PDQ])+0

SWITCH (
TRUE (),
[measure 1 ]=0 "red",
SUM(Append1[PDQ] )< CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)), "pink",
SUM(Append1[PDQ] )> CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)),"lightgreen")

[Anonymous]

Its still not working unfortunately. for example I need this column to be highlighted red. So the first blank after a value:
Also is there anyway to remove the conditional formatting from the first week?