Words in a text string according to a table.

[P-Lag]

Hi!

I need help searching for words in a text string according to a table.

I have used the below DAX expression in a column, and it works, but is it possible to search for words according to another table? I want to avoid to type all words in the expresion.

 Product =

SWITCH (

  TRUE (),

  SEARCH ( "ABC", 'Data'[Equipmentlist], 1, 0 ) > 0, "ABC",

  SEARCH ( "EFG", 'Data'[Equipmentlist], 1, 0 ) > 0, "EFG",

  BLANK()

)

Example

I have the table “Data” and want to search for words in the column “Equipmentlist” - according to the table “Main Products”, and return the result in the column “Products”.

So, if a word in the “Equipmentlist” match the word in the “Equipment” it will return it to “Products”. I hope I made it clear 😊

Table “Data”

Equipmentlist	Product
ABC, X4569, Y894	ABC
H369, EFG, J789, P678, L789, R896	EFG
XXU, JJP, UUF, P678, L789, R896	UUF
M568, LKE, TTY, PPV. K896,	LKE
J789, P678, ABC, L789, R896	ABC
LKE, TTY, PPV. K896,	LKE
X4569, JJP, UUF, P678,	UUF

Table "Main Products"

Equipment

ABC

EFG

UUF

LKE

etc…

Thanks in advance 😊

[tamerj1 · Accepted solution]

Hi @P-Lag 
Please refer to attached sample file with the soluion

Product = 
MAXX ( 
    FILTER ( 
        'Main Products',
        CONTAINSSTRING ( Data[Equipmentlist], 'Main Products'[Product] )
    ),
    'Main Products'[Product]
)