Forum Discussion

PBI_Rookie's avatar
PBI_Rookie
Helper I
5 years ago
Solved

Matrix conditional formatting- Blank values

Hello All,

 

I have been using the below to format values in a matrix. It works well except for null values I have in my table. I've tried a few things without success. Am I a firstnonblank  somewhere away from making this work?

 

Format =
VAR LowerLimitCheck = AVERAGE(Specifications[LSL])-AVERAGE(results[VALUE])
VAR UpperLimitCheck = AVERAGE(results[VALUE])-AVERAGE(Specifications[USL]) RETURN IF(LowerLimitCheck >0, "#f25a38", IF(UpperLimitCheck >0, "#f25a38", "#04bf9d"))
 
 
Thank you
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi PBI_Rookie ,

    Sorry for replying late. Did the blank value not show in the matrix? If so, you need to create a seprate table for this column, create relationships between them and create a measure to + 0 to show:

    Table = DISTINCT('Specifications'[V])
    Measure = SUM('Table'[V]) + 0

    Now the conditonal format rule should work:

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    PBI_Rookie , Also add condition for blank

    IF(LowerLimitCheck >0, "#f25a38",

    Switch( True() ,

    isblank(UpperLimitCheck) , "#04bf9d"

    UpperLimitCheck >0, "#f25a38", "#04bf9d"))

  • PBI_Rookie's avatar
    PBI_Rookie
    Helper I
    5 years ago

    Hello,

     

    I tried what you suggested. I made one change from UpperLimitCheck to Lowerlimit check as that is where the null values  are(in red). I'm still seeing the same issue as before with flagging a null table value. Did I miss something in your suggestion?

     

    Thank you

     

    Format =
    VAR LowerLimitCheck = AVERAGE(Specifications[LSL])-AVERAGE(results[VALUE])
    VAR UpperLimitCheck = AVERAGE(results[VALUE])-AVERAGE(Specifications[USL]) Return IF(LowerLimitCheck >0,"#f25a38",SWITCH(TRUE(),ISBLANK(LowerLimitCheck),"#04bf9d", IF(UpperLimitCheck >0,"#f25a38","#04bf9d")
     
     
    • v-yingjl's avatar
      v-yingjl
      Community Support
      5 years ago

      Hi PBI_Rookie ,

      Sorry for replying late. Did the blank value not show in the matrix? If so, you need to create a seprate table for this column, create relationships between them and create a measure to + 0 to show:

      Table = DISTINCT('Specifications'[V])
      Measure = SUM('Table'[V]) + 0

      Now the conditonal format rule should work:

      Attached a sample file in the below, hopes to help you.

       

      Best Regards,
      Community Support Team _ Yingjie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.