Searching for matching elements with two comma-separated columns

[purple_SP]

Hi all,

I have two tables with a one-to-many relationship. Both have a column with a list of values seperated by a comma:

eg: Table 1 (One side)

Name	Pets
Bob	dog, fish
Sally	cat, chicken
Andy	fish, dog, cat

Table 2 (Many side)

Name	Grocery Shopping
Bob	chicken, beef
Sally	beef, milk, chicken
Andy	cheese, fish

I want to create a calculated column in Table 2 that returns 'Y' if one of the elements in grocery shopping are found in the pets column or "N" otherwise. (I'd prefer to do this in DAX)

eg: 

Name	Grocery Shopping	matching item
Bob	chicken, beef	N
Sally	beef, milk, chicken	Y
Andy	cheese, fish	Y

I feel like one way to do this would be to break the grocery shopping into elements separated by the comma and use CONTAINSSTRING() for each of the elements, but I am unsure how to achieve this in DAX/PBI.

Any help would be much appreciated!

[parry2k · Accepted solution]

@purple_SP or you can add a new column in Table2 using the following expression, change the table name and column name as per your model.

Matching Item = 
VAR __shoppingValue = SUBSTITUTE (Table2[ Grocery Shopping],",","|" )
VAR __totalValues = PATHLENGTH (__shoppingValue )
VAR __petsList = RELATED (Table1[ Pets] )
VAR __matchingTable = 
    ADDCOLUMNS (
        GENERATESERIES (1, __totalValues ), 
        "@IsMatched", CONTAINSSTRING ( __petsList, PATHITEM ( __shoppingValue, [Value],TEXT ) ) + 0 
    )
VAR __matchCount = SUMX ( __matchingTable, [@IsMatched])
RETURN IF ( __matchCount > 0, "Yes", "No" )

👉 Learn Power BI Subscribe to our YT channel - @PowerBIHowTo

[purple_SP]

Thanks @parry2k this also worked for me and is cleaner than what I had.

[purple_SP]

I was able to find a solution to this problem (with many thanks to this post: Solved: DAX To Split into Rows on Delimeter - Microsoft Fabric Community ) by splitting the first list into rows by a delimiter, using CONTAINSSTRING to create a column that is 1 if the item in the first list exists in the second and 0 otherwise, and using SUMX with an if statement to get the final result.