Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional Formatting in concatenated text

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?

  • 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.

6 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support

    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's avatar
      Anonymous
      Not applicable

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

    • Anonymous's avatar
      Anonymous
      Not applicable

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

      • v-xiaotang's avatar
        v-xiaotang
        Community Support

        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.

    • AmitRohilla's avatar
      AmitRohilla
      New Member

      What if  [Readiness of Upcoming Gate(s)] is in text and values like Higher and Lower or Medium ? 

  • What if  [Readiness of Upcoming Gate(s)] is in text not and values like higher, lower or medium ?