Identify if the customer has duplicate accounts from different origin and if completed an order

[Andrew_na_lang]

Hello, Team.

I made a table in which I counted how many times a phone number appeared in the same column more than once. These customers signed up using the same phone number but from different origins. Ideally, we need to identify who these customers are, because a customer should only have one profile, regardless of origin. Also, we need to know who has multiple profiles and has completed at least one order on both origins.

Customers table:

The last column on the right is my desired output on Power Bi. 

Your help is much appreciated.

[tamerj1 · Accepted solution]

Hi @Andrew_na_lang 
Please refer to attached sample file with the solution

PHONE NUMBER APPEARED MORE THAN ONCE = 
IF ( 
    COUNTROWS ( 
        CALCULATETABLE (
            'Table',
            ALLEXCEPT ( 'Table', 'Table'[Phone number =] )
        )
    ) > 1,
    "Y",
    "N"
)

NUMBER APPEARED TWICE and COMPLETED AN ORDER ON BOTH = 
IF (
    COUNTROWS ( 
        CALCULATETABLE ( 
            VALUES ( 'Table'[Origin] ),
            ALLEXCEPT ( 'Table', 'Table'[Phone number =] ),
            'Table'[TOTAL ORDER] <> BLANK ( )
        )
    ) > 1,
    "Y",
    "NO"
)