Forum Discussion
Match between two columns in different order
- 4 years ago
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.
- 4 years ago
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
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.
Thank you so much! This works great