Forum Discussion
Helper ISearching for matching elements with two comma-separated columns
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!
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
3 Replies
purple_SPI 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.
- parry2k
Super User
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