topic Identify if the customer has duplicate accounts from different origin and if completed an order in DAX Commands and Tips

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

Andrew_na_lang — Fri, 03 Feb 2023 11:49:32 GMT

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.

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

tamerj1 — Fri, 03 Feb 2023 12:43:03 GMT

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" )