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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
inglexjc
Helper IV
Helper IV

Color coding with Multiple Colors with Multiple Text in same column

I have a table called 'Legal Files' column name is Description_1 (Visual shows Description).

 

I was able to color code when "Letter Waived" is in the column to show the font as BLUE.  But now it's been asked to keep that AND anytime the word "person" or "WebEx" is used for the font to be RED and "Dispo" to be BLUE. 

When I do Status Color = SWITCH('Legal Files'[Description_1], "Dispo",1, "Letter Waived",2, "Person",3, "WebEx",4)

inglexjc_0-1675287568581.png

This doesn't work because there is other text in the column so it's not exactly just the words being used.

inglexjc_1-1675287605532.png

How else can I get the text to change color when the column contains those words?

1 ACCEPTED SOLUTION

Color =
VAR col = SWITCH(TRUE(),CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"Letter Waived") || CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"Dispo"),"Blue", CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"Person") || CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"WebEx"),"Red")
RETURN
col

View solution in original post

24 REPLIES 24
inglexjc
Helper IV
Helper IV

We have a winner!  Thank you.

Padycosmos
Solution Sage
Solution Sage

In power query, select the description column, create a new column by example, type out the required word in two or three rows, the required words automatically get filled in. You can use the new column in the SWITCH() condition

I'm thinking this isn't going to work.

 

Error even when I tried Letter Waived instead of LW. (in the raw data it comes over as LW.

inglexjc_0-1675366558361.png

I'll have to try something different.

Please check my latest reply. 

Padycosmos
Solution Sage
Solution Sage

@Padycosmos this does not help.  Watching the video it has to do with numbers and doesn't translate to text.  Also in the video the numbes are not in the same Column they are in differnt rows.

 

I need color coding for TEXT in the same Column.

I understand. Please try changing the format style to Field Value as shown below

Padycosmos_0-1675354366419.png

 

That's not working either.

In the conditions, you can use the conditions  "Person" || "Webex", 3, "Letter Waived" || "Dispo",1

@Padycosmos in the youtube video you sent it's using 

inglexjc_0-1675356444904.png

How do I convert this when I'm not using numbers?  What formula should I be using in order to get the conditional formating to work?

I have never tried this with a text column and I do not have sample data at the moment. So you may try changing the conditions as shown below: and use numbers as you have used hitherto.

In the conditions, you can use the conditions

SWITCH(TRUE(),'Legal Files'[Description_1], "Letter Waived" || "Dispo",1,"Person" || "Webex", 2) 

The '||' symbol is in this key Padycosmos_0-1675357488827.png Press Shift+this key twice

 

@Padycosmos 

Unfortunately that didn't work either.

inglexjc_0-1675357956271.png

 

Sorry about that. Please try this.

SWITCH(TRUE(),'Legal Files'[Description_1]= "Letter Waived" || "Dispo",1,'Legal Files'[Description_1]="Person" || "Webex", 2) 

 

 

@Padycosmos  I tired as a new Measure and as a new Column, both are not working.

As a Column:

inglexjc_0-1675358612397.png

As a Measure:

inglexjc_1-1675358647317.png

 

A solution at last. It is a measure

Padycosmos_0-1675362348376.png

Padycosmos_1-1675362409197.png

 

@Padycosmos  The data has PPI so I can not share.

 

What am I doing wrong?

inglexjc_0-1675363624216.png

But also notice there is other text in the Column not just the words I'm calling out, so that's part of the problem I'm having.  I was able to create a measure to make when "Letter Waived" is in the text to make it all blue.  But I'm not able to add if "person" or "webex" is there make it red. 

inglexjc_1-1675363721569.png

 

@Padycosmos  I tried both of these

 

Color =
VAR COL = SWITCH(TRUE(),SELECTEDVALUE('Legal Files'[Description_1]= "Letter Waived"||SELECTEDVALUE('Legal Files'[Description_1])="Dispo","Blue",SELECTEDVALUE('Legal Files'[Description_1])= "Person","Red",SELECTEDVALUE('Legal Files'[Description_1])="WebEx","Red")
RETURN
COL
inglexjc_1-1675365639173.png

 

 
 
Color =
VAR col = SWITCH(TRUE(),SELECTEDVALUE('Legal Files'[Description_1]= "Letter Waived"||SELECTEDVALUE('Legal Files'[Description_1])="Dispo","Blue",SELECTEDVALUE('Legal Files'[Description_1])= "Person","Red",SELECTEDVALUE('Legal Files'[Description_1])="WebEx","Red")
RETURN
col
inglexjc_0-1675365614624.png

 

Padycosmos_0-1675366425272.png

This works even if the text contains other values. 

Padycosmos_1-1675366589296.png

 

@Padycosmos 

Color =
VAR col = SWITCH(TRUE(),SELECTEDVALUE('Legal Files'[Description_1]= "Letter Waived"||SELECTEDVALUE('Legal Files'[Description_1])="Dispo","Blue",SELECTEDVALUE('Legal Files'[Description_1])="Person"||SELECTEDVALUE('Legal Files'[Description_1])="WebEx","Red")
RETURN
col
 
Still get error.

Color =
VAR col = SWITCH(TRUE(),CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"Letter Waived") || CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"Dispo"),"Blue", CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"Person") || CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"WebEx"),"Red")
RETURN
col

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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