Check if value within spec limit

[sbarnhill75]

Hello - 

I have a 'Product' table with all of the products and then a column for their [Min Temperatures] and another column of [Max Temperatures].  Between the min and max temps is an acceptable range.  Then I have another table with hourly actual readings 'Details'.  This has a [Date Time], [Product] and [Temperature] column.  The two tables are joined by the [Product] and everything works okay.  If I just pull in the min and max columns to the details table it appears how I expect it to.

What I cannot seem to do is figure out a DAX formula to use for conditional formatting on the 'Details'[Temperature] column.  I want to show this as red if over max or blue if under min.  Basically I've been trying something like this:  if('Details'[Temperature]<'Products'[Min temperature],1, if('Details'[Temperature]>'Products'[Max temperature],2,3)).  It doesn't matter if it is a measure or a calculated column but everything I try doesn't seem to work.

Thank you - 

Sara

[mwegener]

Hi @sbarnhill75 ,

you must specify in the formula how the values are to be aggregated, if necessary. I would work here with MIN and MAX. You can already specify the color here as a HEX code.

CF Temperature =
IF (
    MAX ( Details[Temperature] ) > MAX ( 'Product'[Max Temperatures] ),
    "#FF0000",
    IF (
        MIN ( Details[Temperature] ) < MIN ( 'Product'[Min Temperatures] ),
        "#0000FF",
        BLANK ()
    )
)

[sbarnhill75]

That did not seem to work.  I thought that it might work because it was different than the combinations I've been trying.  I'm working in Visual Studio so I didn't use the HEX code parts, but ended up entering in the formula like this:  

Temp in Range2:=IF(

MAX('Details'[Temperature]) > MAX('Products'[PR_MAX_T]), 1,
IF(MIN('Details'[Temperature]) < MIN('Products'[PR_MIN_T]), 2,
3))

And here are my results...

the PR_MIN_T AND PR_MAX_T are the ranges for those products

[v-easonf-msft]

Hi, @sbarnhill75 

Try calculated columns as below:

Max_Tem = RELATED('Product'[Max Temperatures]) 

Min_Tem = RELATED('Product'[Min Temperatures]) 

conditional formatting =
SWITCH (
    TRUE (),
    Details[Temperatures] > Details[Max_Tem], "orange red",
    Details[Temperatures] < Details[Min_Tem], "light blue",
    "light yellow"
)

Best Regards,
Community Support Team _ Eason

[mwegener]

Hi @sbarnhill75 ,

can you share a screenshot of the data model and data in the data view?

[sbarnhill75 · Accepted solution]

This is what we ended up getting to work and maybe it will be helpful to someone else.

=
VAR MAXVal = IF(ISBLANK(RELATED('Products'[PR_MAX_Temp])), 10000, RELATED('Products'[PR_MAX_Temp]))
RETURN
IF(
('Details'[Temperature]) > MAXVal, "#FF0000",
IF((('Details'[Temperature]) < RELATED('Products'[PR_MIN_Temp])), "#0000FF", "#198c19"))