Forum Discussion

rlfRodrigo's avatar
rlfRodrigo
Frequent Visitor
2 years ago
Solved

Find whats different between elements of a string

Im having trouble figuring out how to get it done.

Need to find the difference between elements of two strings using DAX

A dummy table to ilustrate what i have:

 

What i need on C:

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution rajendraongole1  provided, and i want to offer some more information for user to rerfer to.

    hello rlfRodrigo , you can refer to the following calculated column.

    Column = 
VAR a =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [A] ), 1 ),
            "Value_A", PATHITEM ( [A], [Value] )
        ),
        "ValueA", [Value_A]
    )
VAR b =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [B] ), 1 ),
            "Value_B", PATHITEM ( [B], [Value] )
        ),
        "ValueB", [Value_B]
    )
RETURN
    CONCATENATEX ( EXCEPT ( a, b ), [ValueA], "|" )

    Output

     

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi rlfRodrigo -To find the difference between elements of two strings in DAX and produce

    create a below calculated column

    Difference =
    VAR ListA = SUBSTITUTE(PIPE[A], "|", ",")
    VAR ListB = SUBSTITUTE(PIPE[B], "|", ",")
    VAR TableA = ADDCOLUMNS(
                     SELECTCOLUMNS(
                         GENERATESERIES(1, LEN(ListA)),
                         "CharA", MID(ListA, [Value], 1)
                     ),
                     "IsInB", SEARCH([CharA], ListB, 1, 0)
                 )
    VAR Result = CONCATENATEX(
                     FILTER(TableA, [IsInB] = 0),
                     [CharA],
                     "|"
                 )
    RETURN Result
     

     

    Hope it helps

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • rlfRodrigo's avatar
    rlfRodrigo
    Frequent Visitor
    2 years ago

    That works perfectly when I have 1 character per delimiter, but how can I modify it to get multiple characters? (the idea is to use it with series of 7 characteres) 

    Like this:

    In this case, C would be:
    CC|AA|22
    BB|GG|77

    The modification I got half working was this one:

    But apparently it removes any character found within B:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      Thanks for the solution rajendraongole1  provided, and i want to offer some more information for user to rerfer to.

      hello rlfRodrigo , you can refer to the following calculated column.

      Column = 
VAR a =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [A] ), 1 ),
            "Value_A", PATHITEM ( [A], [Value] )
        ),
        "ValueA", [Value_A]
    )
VAR b =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [B] ), 1 ),
            "Value_B", PATHITEM ( [B], [Value] )
        ),
        "ValueB", [Value_B]
    )
RETURN
    CONCATENATEX ( EXCEPT ( a, b ), [ValueA], "|" )

      Output

       

       

       

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Just in case you are interested, this M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Difference(Text.Split([Text1],"|"),Text.Split([Text2],"|")),"|"))
in
    #"Added Custom"

    Hope this helps.

     

    • rlfRodrigo's avatar
      rlfRodrigo
      Frequent Visitor
      2 years ago

      I thought about using M but both of my columns came from another calculated column so it wasnt possible without export/import

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi rlfRodrigo 

        You can refer to the solution I offered above. it gives the result you want.

         

        Best Regards!

        Yolo Zhu