Conditional Formatting in concatenated text

[Anonymous]

Hey there, looks like I need your help again. These forums are something else, never failed me before.

1. What do we have? 

 There are gates, an there is thing called "readiness". It reflects whether "we" ready to move to the next gate or not. They have their own thresholds. The visual itself are table visual and the columns here are measures based on table columns:

Why not just pull column to table visual?

Sometimes, there are 2, 3 gates that should be reached at the same time.

 And they have merged readiness via "concatenation". 

2. WHAT I WANT:

It is easy to conditionally format when there are but one value present (formatting based on a measure though):

But how do I format them when they are merged like this? Keeping in mind that every gate has its own min and max thresholds.

Conditional formatting measure example:

Conditional Formatting: A = 
VAR MAX_a_condition =
    MAXX (
        FILTER ( 'Readiness Table', 'Readiness Table'[Gate] = "Start A" ),
        'Readiness Table'[max_theshold]
    )
VAR MIN_a_condition =
    MAXX (
        FILTER ( 'Readiness Table', 'Readiness Table'[Gate] = "Start A" ),
        'Readiness Table'[min_threshold]
    )

RETURN
    SWITCH (
        TRUE (),
        OR( ISBLANK([A Readiness]), [A Readiness] = "Not Applicable"), BLANK(),
        [A Readiness]  < MIN_alpha_condition , "FlagLow",
        [A Readiness]  > MAX_alpha_condition , "FlagHigh",
        "FlagMedium"
    )

It is okay if there is no way of doing it... But there might be some?

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

>>  ...they have merged readiness via "concatenation". ....It is easy to conditionally format when there is one value.... But how do I format them when they are merged?

One idea: 

first, you need to judge whether the [Readiness of Upcoming Gate(s)] contains one value or two values. You can use length() to judge here. If the length exceeds a certain value, it contains two values. You can also use CONTAINSSTRING(), e.g.

Then, if it's one value, use the original code; if it's two values, you need to split it. For example, if the value is less than 18%, set the background color to yellow

create the measures,

max thresholds = 
var _row= MIN('Table'[Readiness of Upcoming Gate(s)])
return IF(LEN(_row)>8,value(LEFT(_row,SEARCH(",",_row)-2))/100)

min thresholds = 
var _row= MIN('Table'[Readiness of Upcoming Gate(s)])
var _v1=IF(LEN(_row)>8,RIGHT(_row,SEARCH(",",_row)-1))
var _v2=LEFT(_v1,LEN(_v1)-1)
return IF(LEN(_row)>8,_v2/100)

Measure = 
var _row= MIN('Table'[Readiness of Upcoming Gate(s)])
var _color= "yellow"
return IF(LEN(_row)>8,IF([max thresholds]<0.18|| [min thresholds]<0.18,_color))

put [Measure] into  conditional formatting

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

What if  [Readiness of Upcoming Gate(s)] is a measure, and not a column to do MIN function on? 

[v-xiaotang]

Hi @Anonymous 

if  [Readiness of Upcoming Gate(s)] is a measure, then please remove min()

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I will try it out first thing tomorrow and let you know if it works, THANK YOU!