Identify text in columns that matches partial text from another column

[mmarchioni]

I would like to highlight the text in columns 1, 2, and 3 that match partial text in column 4 within the same row.

For example, in the Col1, DR7 should be highlighted. In Col2, A2 and A1 sould be highlighted. In Col3, B57 should be highlighted.

Any help in how to do this would be appreciated.

Col1	Col2	Col3	Col4
DR53	A2	B8	DR52(123),A2(456),B6(789)
DR7	A1	B57	DR7(765),A1(2400),B57(34)

[v-jingzhang]

Hi @mmarchioni 

You could create three similar measures like below (change column names) and apply them in conditional formatting to three columns separately. 

Color Col1 = 
VAR value_Col1 = SELECTEDVALUE('Table (2)'[Col1])
VAR value_Col4 = SELECTEDVALUE('Table (2)'[Col4])
RETURN
IF(SEARCH(value_Col1,value_Col4,,-1)>0,1,-1)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[mmarchioni]

I tried the measure. Some rows of Col 1 highlighted, but when they shouldn't have.

For instance

Col1

A2 was highlighted

but 

Col4 in that row had

A36(2167).A1(2153)

It should only highlight when "A2" is in Col4.

[v-jingzhang]

Hi @mmarchioni 

I created three measures in the sample pbix:  Color col1, Color col2 and Color col3. And in conditional formatting, select Color col1 for Col1 column, Color col2 for Col2 column, Color col3 for Col3 column. Please check if the correct measure is selected in Based on field dropdown box for each column.

Based on my test, it isn't highlighted.

Jing

[mmarchioni]

I confirmed, the correct measure is chosen in the Based on Field in the conditional formatting. 

Here is the measure.

Color A1 =

VAR value_Col1 = SELECTEDVALUE(rvTable[Col1])

VAR value_Col4 = SELECTEDVALUE(rvTable[Col4])

RETURN

IF(SEARCH(value_Col1,value_Col4,,-1)>0,1,-1)

[v-jingzhang]

Hi @mmarchioni 

Are these two columns from the same table? Can you provide some sample data to help me understand how they look like in the original table rather than in a table visual?

I guess the probable cause is that the two SELECTEDVALUE functions don't return the correct values in Col1 and Col4 on the same row, so the SEARCH part fails. You can create two separate measures ValueCol1 = SELECTEDVALUE(rvTable[Col1]) and ValueCol4 = SELECTEDVALUE(rvTable[Col4]), then put them into the same table visual to check whether they return the correct results of Col1 & Col4 on each row.

Let me know the result of above test. Thanks.

Jing

[mmarchioni]

I put in the two measures as you described.

ValueCol1 matched Col1, however, ValueCol4 did not always match Col4????? I have no idea why.

[amitchandak · Accepted solution]

@mmarchioni , You have to create measures like below and use them in conditional formatting using filed value option. You have to use a table not a matrix

If (search([Col1],[col4],,0)>0, "yellow", "white")

Create for other 2 columns too

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[mmarchioni]

Tried to create the measure above and it did not work. It doesn't allow me to choose my columns.