Find if value contains anything from another column

[jasmith866]

I have two tables - example:

Table 1

Field 1

Cat

Dog

Fish - Big

Fish - Small

Antelope

Giraffe - Massive

Table 2

Field 1

Cat

Fish

Giraffe

I want to flag the first table if any of the values in field 1 contain anything in Field 1 from table 2 to get to:

Table 1

Field 1

Cat (TRUE)

Dog (FALSE)

Fish - Big  (TRUE)

Fish - Small  (TRUE)

Antelope (FALSE)

Giraffe - Massive (TRUE)

I have tried using the CONTAINS function, but this is only workign for me with exact matches, and not allowing for partial ones - is there a solution for this?

[Anonymous · Accepted solution]

Hi @jasmith866 ,

You could try this calculated column.

T/F = 
IF (
    SUMX (
        'Table (2)',
        FIND ( UPPER ( 'Table (2)'[Keywords] ), UPPER ( 'Table'[Field] ),, 0 )
    ) > 0,
    "True",
    "False"
)

Reference: DAX – “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jasmith866]

Brilliant - thank you for finding this, seems to be working well in my project.

I don't suppose you can think of a way to adapt it to return the value that it's matched, and not just whether or not it has? Presumably that would need to be a completely different solution, as SUMX/SEARCH won't work that way

[Greg_Deckler]

@jasmith866 - I believe you need to use FIND or SEARCH. Maybe CONTAINSSTRING

[jasmith866]

Thanks, I did have a look at those - but they only seem to accept single strings to search with, rather than the whole field

[amitchandak]

@jasmith866 , check if this can work

new column = if(isblank(countx(filter(Table1,CONTAINSSTRING(Table1[Field1], Table2[Field1]) ),Table1[field1])), "FALSE","TRUE")

[jasmith866]

Thanks, but that doesn't seem to work - CONTAINSSTRING only accepts a single string for the search criteria so I can't pass it the column in table 2

[warnera]

I am having simliar question and have been unable to come up with solution. In addition to your request, I am trying to have the desired column pull the string of text that is simliar between the two fields whereas in your example you are looking only for TRUE FALSE.

I will follow this thread to see if any ideas arise. This command didn't work for me either where Column A = Fred, Sam, Steve and Column B = Sam, Mike, Steve and Desired column = Sam, Steve

Text.Combine(List.Intersect(Text.Split([Column A],",") ,Text.Split([Column B],",")),",")