Matrix Conditional Formatting

[PBI_Rookie]

Hello,

I have a matrix with the following. I'm trying to highlight values in the Measured column if it is > or < the limits(LSL, USL).The matrix varies by filters so a static value or percentage won't work.  I'm missing something on setting up the dax and keep hitting errors. 

Any help is appreciated. 

Thanks

[PBI_Rookie]

Allison, 

That solved it!

A little more work to figure out what to do with a (blank - a value)  using this format but this is workable!

Thank you

[amitchandak]

@PBI_Rookie , You can create a color meausre like the given example

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

color = Switch( True(), [Meausre]<[LSL] , "Red",

[Meausre]>=[LSl] && [Meausre]<[USL],"Orange",

"green"

)

And then use it in conditional formatting with "field value: option 

refer: https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[AllisonKennedy]

You should be able to create another Measure that calculates:
Format =
VAR LowerLimitCheck = [LSL]-[Measured]
VAR UpperLimitCheck =[Measured]-[USL]
RETURN IF(LowerLimitCheck >0, "Below LSL", IF(UpperLimitCheck >0, "Above USL", "Within limits"))
You can change the "Above USL" and "Below LSL" and "Within Limits" to other text or colours or numbers and then apply conditional formatting based on that measure.

[PBI_Rookie]

I tried what you suggested Allison.

I replaced your varibles with the names of my tables and columns.

Format =

VAR LowerLimitCheck =(vw_Specifications[LSL]-vw_QAresults[VALUE]

VAR UpperLimitCheck =(vw_QAresults[VALUE]-vw_Specifications[USL]

RETURN IF(LowerLimitCheck >0, "Below LSL", IF(UpperLimitCheck >0, "Above USL", "Within limits"))

I got the following error

I thought I could solve that by adding Average  as my aggregation ex: VAR LowerLimitCheck = Average(vw_Specifications[LSL]-Average(vw_QAresults[VALUE] and get a Var syntax error. 

[AllisonKennedy · Accepted solution]

Ah, yes, sorry I thought your LSL and USL were measures. Since they are columns average aggregation should work, you just need to add brackets;

VAR LowerLimitCheck = Average(vw_Specifications[LSL])-Average(vw_QAresults[VALUE])