Forum Discussion

jasmith866's avatar
jasmith866
Icon for Helper I rankHelper I
6 years ago
Solved

Find if value contains anything from another column

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?

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      jasmith866
      Icon for Helper I rankHelper I
      6 years ago

      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

    • jasmith866's avatar
      jasmith866
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        jasmith866 , check if this can work

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