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  somewh...
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.

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.