Forum Discussion
Conditional Formatting on Blank Matrix Cells
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.
9 Replies
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")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 resultAnonymous , 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")
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.
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.
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
