Forum Discussion

sbarnhill75's avatar
sbarnhill75
Frequent Visitor
5 years ago
Solved

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 ra...
Need Help
  • sbarnhill75's avatar
    sbarnhill75
    5 years ago

    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"))

     

mwegener's avatar
mwegener
Most Valuable Professional
5 years ago

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's avatar
sbarnhill75
Frequent Visitor
5 years ago

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

 

 

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    5 years ago

    Hi sbarnhill75 ,

     

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

     

    • sbarnhill75's avatar
      sbarnhill75
      Frequent Visitor
      5 years ago

      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"))

       

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    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