Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Compare Names between two columns and return difference

I have a dataset that contains users in and all users. I want to be able to compare these columns and return a column that shows users that are in 

 

 

Expected Result:

 

 

 

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  solely with a dax measure.

     

    _missingValues =
VAR _1 =
    ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
            "Words", PATHITEM ( [_allUsers], [Value], TEXT )
        )
    )
RETURN
    CONCATENATEX (
        FILTER (
            _2,
            [Users off] <> [Words]
                && CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()
        ),
        [Words],
        ",",
        [Words]
    )

     

     

     

    And a calculated column

     

     

    Column = 
VAR _1 =
    
        ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
        
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
            "Words", PATHITEM ( [_allUsers], [Value], TEXT )
        )
    )
RETURN
    CONCATENATEX (
        FILTER (
            _2,
            [Users off] <> [Words]
                && CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()&&'Table'[Index]=EARLIER([Index])
        ),
        [Words],
        ",",
        [Words]
    )

    PBIX is attached

     

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , A new column in power query

     

    List.RemoveItems(Text.Split([All User],","),Text.Split([All User],","))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak  sorry my current columns are via a DAX Column. Therefore this would need to be done in DAX

      • v-yingjl's avatar
        v-yingjl
        Community Support
        4 years ago

        Hi Anonymous ,

        Agree with @ amitchandak, it is better to use Power Query to achieve it because the string comparison is strictly defined in DAX and split columns is too complex.

         

        Since the two user columns have been created by DAX, you can consider using the simliar logic to create them in Power Query first and refer this simliar thread to compare string columns to find the difference in Power Query:

        Comparing String in two columns for differences 

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  solely with a dax measure.

     

    _missingValues =
VAR _1 =
    ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
            "Words", PATHITEM ( [_allUsers], [Value], TEXT )
        )
    )
RETURN
    CONCATENATEX (
        FILTER (
            _2,
            [Users off] <> [Words]
                && CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()
        ),
        [Words],
        ",",
        [Words]
    )

     

     

     

    And a calculated column

     

     

    Column = 
VAR _1 =
    
        ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
        
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
            "Words", PATHITEM ( [_allUsers], [Value], TEXT )
        )
    )
RETURN
    CONCATENATEX (
        FILTER (
            _2,
            [Users off] <> [Words]
                && CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()&&'Table'[Index]=EARLIER([Index])
        ),
        [Words],
        ",",
        [Words]
    )

    PBIX is attached

     

     

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous did you try the above?