Find non-duplicates/compare to columns to return non-duplicates

[DennisNilsson]

Hey, I have two columns i wish to compare, from two different tables. 

Both columns are text and I would like a formula to return all non-duplicates. 

First column is basically customer names from an old system, second column is customer names from the new system. I know that not all customers from the old system are yet registrered in the new system.

Therefore I would like to create a visual displaying the names of customers not yet registrered in the new system.

It would be perfect for me if this could be done with a DAX expression so that the visual would get updated once I refresh the data.

Anyone that could help me?

[v-yulgu-msft]

Hi @DennisNilsson,

Please new a calculated table with below DAX formula:

non duplicate = EXCEPT(VALUES('Old system'[Old customer name]),VALUES('New system'[New customer name]))

Then, you can use a table visual to display above returned data.

Best regards,

Yuliana Gu

[DennisNilsson]

Thank you @v-yulgu-msft, this works well!

Is there also a way to only return results where Active field in Old system table = 1?

[v-yulgu-msft · Accepted solution]

Hi @DennisNilsson,

non duplicate =
EXCEPT (
    CALCULATETABLE (
        VALUES ( 'Old system'[Old customer name] ),
        'Old system'[Active] = 1
    ),
    VALUES ( 'New system'[New customer name] )
)

Best regards,

Yuliana Gu

[Greg_Deckler]

Perhaps:

Column = IF([OldName] = [NewName],BLANK(),[NewName])

[DennisNilsson]

"IF(
[Name] = Portals[Name],
BLANK (),
Portals[Name]) "

returns: Expression.Error: The name "IF" wasn't recognized. Make sure it's spelled correctly.

[DennisNilsson]

Thank you @Greg_Deckler, problem is however that the logical test part of the IF function can only find measures? I need it to refer to a "normal column" or field if you wish.  Is that possible?