Match between two columns in different order

[mstoppelman]

Hi, I've got two calculated columns, so a Power Query solution isn't feasible, and I don't really know where to start, or if it's possible to achieve my expected result. The two columns have data separated by commas, and I want to check if what's between the commas matches, even if the items are in a different order.

Here is a visual of what I'm looking for, let's say this table is called "test"

Calculated Column1	Calculated Column2	Expected Result
ABC,DEFGH,IJ	IJ,ABC,DEFGH	Match
XYZ,MNOPQR	LMN,TUVWXYZ	No Match

[AlexisOlson · Accepted solution]

This is annoying to do in DAX since there isn't a nice function to split the text. However, it's possible to abuse the PATH functions to accomplish this.

Expected Result = 
VAR Path1 = SUBSTITUTE ( Concat[Calculated Column1], ",", "|" )
VAR Path2 = SUBSTITUTE ( Concat[Calculated Column2], ",", "|" )
VAR Len1 = PATHLENGTH ( Path1 )
VAR Len2 = PATHLENGTH ( Path2 )
VAR List1 = SELECTCOLUMNS ( GENERATESERIES ( 1, Len1 ), "Item", PATHITEM ( Path1, [Value] ) )
VAR List2 = SELECTCOLUMNS ( GENERATESERIES ( 1, Len2 ), "Item", PATHITEM ( Path2, [Value] ) )
RETURN
    IF ( MAX ( Len1, Len2 ) = COUNTROWS ( INTERSECT ( List1, List2  ) ), "Match", "No Match" )

This turns each concatenation into a path and then constructs a list of each item in the path. Then if the intersection of these lists has as many items as each individual one, it's a match.

[mahoneypat · Accepted solution]

This would be much easier in Power Query.  Is it possible to make your Calculated Columns there instead, so you can do this too there?  Or is it indepent and you could do it there first?

Pat

[AlexisOlson · Accepted solution]

This is annoying to do in DAX since there isn't a nice function to split the text. However, it's possible to abuse the PATH functions to accomplish this.

Expected Result = 
VAR Path1 = SUBSTITUTE ( Concat[Calculated Column1], ",", "|" )
VAR Path2 = SUBSTITUTE ( Concat[Calculated Column2], ",", "|" )
VAR Len1 = PATHLENGTH ( Path1 )
VAR Len2 = PATHLENGTH ( Path2 )
VAR List1 = SELECTCOLUMNS ( GENERATESERIES ( 1, Len1 ), "Item", PATHITEM ( Path1, [Value] ) )
VAR List2 = SELECTCOLUMNS ( GENERATESERIES ( 1, Len2 ), "Item", PATHITEM ( Path2, [Value] ) )
RETURN
    IF ( MAX ( Len1, Len2 ) = COUNTROWS ( INTERSECT ( List1, List2  ) ), "Match", "No Match" )

This turns each concatenation into a path and then constructs a list of each item in the path. Then if the intersection of these lists has as many items as each individual one, it's a match.

[mstoppelman]

Thank you so much! This works great