Hello! I need some help with coniditional formatting. I have checked through all other posts on the topic before I have posted this but think my query may be slightly different. I have a list of URLs pulled through from search results and would like to do some conditional formatting with text to recolour the background, based on specific text that could appear in part of the cell. For example:Contains the word 'video' > pink backgroundContains the word 'photo' > orange backgroundContains a specific url > green background etc I have found this post which I think goes halfway to solving the problem.https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Rows/m-p/550047?advanced=false&collapse_discussion=true&filter=location&location=category:PBI_Comm_Forums&q=conditional%20formatting&search_type=threadSo it makes sense to create a table to match my desired key words with a value 1 = video2 = photo However it appears in the example data - all of the lines are tagged with the exact reference. In my case, as my data is being pulled from search results my text could just sometimes part of the of the text displayed. For example https://video.comhttps://www.video.comwww.video-website.com https://photo.comhttps://www.photo.comwww.photo-website.com So I think I might need some code that says 'if Text.Contains' ?Then reference to a table as the example in the above link Then follow the conditional formating to style by number as shown in the link? Many thanksDavid

Hi thinkvisual You may have a look at FIND function. Column = IF ( FIND ( "video", [URL], 1, -1 ) > 0, 1, // If find 'video',the column get 1 IF ( FIND ( "photo", [URL], 1, 0 ) > 0, 2, 3 ) ) // If find 'photo',get 2,otherwise get 3 Regards,

thinkvisual

Frequent Visitor

7 years ago

Solved

Conditional formatting background colour for 'if text contains'

Hello!

I need some help with coniditional formatting. I have checked through all other posts on the topic before I have posted this but think my query may be slightly different. I have a list of URLs pulled through from search results and would like to do some conditional formatting with text to recolour the background, based on specific text that could appear in part of the cell.

For example:

Contains the word 'video' > pink background

Contains the word 'photo' > orange background

Contains a specific url > green background etc

I have found this post which I think goes halfway to solving the problem.

https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Rows/m-p/550047?advanced=false&collapse_discussion=true&filter=location&location=category:PBI_Comm_Forums&q=conditional%20formatting&search_type=thread

So it makes sense to create a table to match my desired key words with a value

1 = video

2 = photo

However it appears in the example data - all of the lines are tagged with the exact reference. In my case, as my data is being pulled from search results my text could just sometimes part of the of the text displayed. For example

https://video.com

https://www.video.com

www.video-website.com

https://photo.com

https://www.photo.com

www.photo-website.com

So I think I might need some code that says 'if Text.Contains' ?

Then reference to a table as the example in the above link

Then follow the conditional formating to style by number as shown in the link?

Many thanks

David

Need Help

v-cherch-msft

7 years ago

Hi thinkvisual

You may have a look at FIND function.

Column =
IF (
    FIND ( "video", [URL], 1, -1 ) > 0,
    1, // If find 'video',the column get 1
    IF ( FIND ( "photo", [URL], 1, 0 ) > 0, 2, 3 )
) // If find 'photo',get 2,otherwise get 3

Regards,

Forum Discussion

Conditional formatting background colour for 'if text contains'

5 Replies

Related content

Background Colour - conditional formatting

Conditional Formatting using DAX

Conditional Formatting

Conditional Formatting

Matrix Row Conditional Formatting