Forum Discussion
Check if value within spec limit
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
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"))
5 Replies
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 () ) )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
