Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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? 

1..jpg

 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:

PreacherBaby_0-1659422145339.png


Why not just pull column to table visual?

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

3.png

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

PreacherBaby_1-1659422613679.png

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.

 

PreacherBaby_2-1659422665496.png

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?

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
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.

vxiaotang_0-1659680837072.png
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

vxiaotang_0-1659691718152.png

result

vxiaotang_1-1659690113156.png

 

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.

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
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.

vxiaotang_0-1659680837072.png
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

vxiaotang_0-1659691718152.png

result

vxiaotang_1-1659690113156.png

 

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
Not applicable

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

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.