Forum Discussion

PBI_Rookie's avatar
PBI_Rookie
Helper I
4 years ago
Solved

Inconsistent Conditional Formatting

I have a measure to hightlight values if they are outside spec limits within a matrix. 

 

This has been working great for one set of products(Product 1). For whatever reason the upper spec values for another product(product 2) type simply will not format correctly. The spec limits for all products are within a single table/column. 

 

As a double check I added the values for product 1  to the same matrix as product 2 verify it wasn't a filter or something i was missing. It worked as expected for those product 1 but not for product 2. 

 

 

Product 2 behavior

 

My Measure: 

VAR LowerLimitCheck = CALCULATE(AVERAGE(Specifications[LSL])-AVERAGE(Details[VALUE]),
Specifications[LSL] > 0)
VAR UpperLimitCheck = CALCULATE(AVERAGE(Details[VALUE])-AVERAGE(Specifications[USL]),Specifications[USL]>0)
Return IF(LowerLimitCheck >0,"#f25a38",SWITCH(true,ISBLANK(LowerLimitCheck),"#04bf9d",IF(UpperLimitCheck >0,"#f25a38","#04bf9d") ))
 
What could I be missing?
 
Thanks!
Need Help
  • PBI_Rookie's avatar
    PBI_Rookie
    4 years ago

    Hrm, they really should be independent conditions. In the end the result needed is that if it is either below the low spec or above the upper spec I need it to flag. 

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    PBI_Rookie , what I getting from it. That month of the time it will work on a lower limit >0 or blank. it means when lower limit <0 it will check upper limit condition

     

    I am assuming Specifications[LSL]  is a column and not using filter clause is intentional

    refer the diff, if needed -http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

    • PBI_Rookie's avatar
      PBI_Rookie
      Helper I
      4 years ago

      Hrm, they really should be independent conditions. In the end the result needed is that if it is either below the low spec or above the upper spec I need it to flag. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi PBI_Rookie ,

        Sorry that I'm still not clear about your requirement. Could you please share your pbix file with simplified data model if it is possible? Later we will check your file and provide a suitable solution base on your file. Thank you.

        Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi PBI_Rookie ,

    Could you please provide some sample data in table Specifications and Details (exclude sensitive data)and your expected result for product 2 with specific sample and screenshot? Is there any relationship created between these two tables? If yes, please also provide the related information. Thank you.

    By the way, the following understanding for the calculation logic of conditional formatting is correct or not? 

    • if LowerLimitCheck >0 or UpperLimitCheck >0, dispaly the data with the color "#f25a38"
    • if  LowerLimitCheck is blank,  dispaly the data with the color "#04bf9d"
    • if UpperLimitCheck <=0 or UpperLimitCheck  is blank, dispaly the data with the color "#04bf9d"

    Best Regards