Comparing Column Values (Column A vs Column B)

[rozoan]

Hi there! 

I've data coming from two different spreadsheets into my report that has employee names column. How can I compare employee names from source #1 to names in source#2 and generate a list of unique names in source #2? Source #2 has the accurate list of names. 

Any help would be appreciated! Thanks! 

[v-frfei-msft · Accepted solution]

Hi @rozoan,

I made one sampe using the formula as dedelman_clng provided. 

MissingEmpName = 
CALCULATETABLE(
    EXCEPT(
       Values('Source#2'[EmpName]),
       Values('Source#1'[EmpName])
    )
) 

For more details, please check the pbix as attached.

https://www.dropbox.com/s/mjvefqwjevxw67f/Comparing%20Column%20Values%20%28Column%20A%20vs%20Column%...

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @rozoan,

I made one sampe using the formula as dedelman_clng provided. 

MissingEmpName = 
CALCULATETABLE(
    EXCEPT(
       Values('Source#2'[EmpName]),
       Values('Source#1'[EmpName])
    )
) 

For more details, please check the pbix as attached.

https://www.dropbox.com/s/mjvefqwjevxw67f/Comparing%20Column%20Values%20%28Column%20A%20vs%20Column%...

Regards,

Frank

[rozoan]

Thank you David & Frank! Both solutions worked! 

I appreciate your help!

[v-frfei-msft · Accepted solution]

Hi @rozoan,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank

[dedelman_clng]

If Source#2 has the accurate list of names, what values do you want from Source#1 ? There are UNION, INTERSECTION and DISTINCT functions, but it depends on what you're really look for.  A small example set of data would be helpful.

David

[rozoan]

Thanks! The goal is to create a table showing names from source #2 that are not in source #1..

[dedelman_clng]

In DAX, create a new table with the following code

MissingEmpName =
CALCULATETABLE(
    EXCEPT(
       Values(Source#2[EmpName]),
       Values(Source#1[EmpName])
    )
)
        

This should give you a table with a single column of names in Source#2 that are not in Source #1.

Hope this helps,

David