Inconsistent Conditional Formatting

[PBI_Rookie]

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!

[Anonymous]

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

[amitchandak]

@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 · Accepted solution]

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]

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