Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Match between two columns in different order

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 sep...
  • AlexisOlson's avatar
    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.

  • mahoneypat's avatar
    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