Conditional formatting with dynamic min and max

[Anonymous]

Hello, I need to design a report where I need to put a table which will show: 1. Date_time (Date and time when the record has been captured) 2. Value 1 3. Value 2 . . . 11. Value 10 Also I have min and max data fields for each value (i.e. Value1_min, Value1_max, Value2_min, Value2_max etc.) These values are dynamic as the values specs (min and max) are changing over time. The issue is that I need to add a conditional formatting in my table where: I put blue for values below min, white for the values within specs and red for the values above max. TI can only put manually the minimum and maximum numbers in conditional formatting which will not work for me as I will need to change the numbers every time the specs are changing. It would be much more easy to put a data field (i.e. Value1_min, Value1_max ) instead of a number in conditional formatting. Does anyone has any idea on how to overcome this??

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may create a measure and use it for 'Rules' as below picture. Attached the sample file for your reference.

Measure =
IF (
    MAX ( ConditionTable[Value] ) < [Value1_min],
    1,
    IF ( MAX ( ConditionTable[Value] ) > [Value1_max], 2, 3 )
)

Regards,

Cherie

[AgustinBrocardo]

Hi @v-cherch-msft 

I was trying to do the same but with gradient style of formatting. Is there any way in which i can set a variable limit for the gradient?

Thanks!

[Anonymous]

Many thanks Cherie,

This looks to work in a calculated column 

Calc. column= if([value]<[value_min], 1, if([value]>[value_max],2,3))

[v-cherch-msft]

Hi @Anonymous

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

Regards,

Cherie