Compare Names between two columns and return difference

[Anonymous]

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:

[smpa01 · Accepted solution]

@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]

@Anonymous did you try the above?

[amitchandak]

@Anonymous , A new column in power query

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

[Anonymous]

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

[v-yingjl]

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.