Conditional formatting background colour for 'if text contains'

[thinkvisual]

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&co...

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

[v-cherch-msft]

Hi @thinkvisual 

You may create a column with below formula.Then use the conditional formatting.

Column =
IF (
    FIND ( "video", [URL], 1, -1 ) > 0,
    1,
    IF ( FIND ( "photo", [URL], 1, 0 ) > 0, 2, 3 )
)

Regards,

[thinkvisual]

@v-cherch-msft Wonderful thank you. I have started working with some DAX today and i'm learning. Would you be able to explain what the numbers are doing in your code so I can understand what is happening please?

Column =
IF (
    FIND ( "video", [URL], 1, -1 ) > 0,
    1,
    IF ( FIND ( "photo", [URL], 1, 0 ) > 0, 2, 3 )
)

Thanks

David

[v-cherch-msft · Accepted solution]

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]

Thank you so much for your help, much appreciated. 

David

[v-cherch-msft]

Hi @thinkvisual 

Kindly mark my answer as a solution if you feel that makes sense.

Regards,