Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 ...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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?