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

     

     

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?