Find out duplicate values from composite key

[Anonymous]

Hello All,

I have three columns in my table Region, Customer id and customer Name. Combination of Region and Customer ID will be a unique identifier for customer.  For ex. if customer "ABC" is in two regions then they will have two different customer IDs. Now i want to identify if there is any duplicates with the above mentioned combination. for ex. cusotmer ID "25" was assgined to two different customers("ABC","XYZ") who are under same region. Please advise is there a way to identify customers who have same customer ID under same region.

Also to identify quantity for each customer i am using the below measure.

totalquantity=calculate(sum(quan[quantity]),filter(quan, quan[region]=customer[region] && quan[customer ID]= customer[customer ID]))

since there are some duplicates in the table(two different customers with same region and same customer ID), above measure is not giving the correct results.

is there a way we can create some unique key for all the customers based on region and customer ID and customer name

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample.

In the sample, the count of all duplicates is 5, the count of distinct customer ID in the duplicates is 2, here's my solution.

Create two measures.

ALL Dup =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
                    && 'Table'[Region] = EARLIER ( 'Table'[Region] )
            )
        ) > 1
    )
)

Count Dup ID =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer ID] ),
    FILTER (
        ALL ( 'Table' ),
        COUNTROWS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
                    && 'Table'[Region] = EARLIER ( 'Table'[Region] )
            )
        ) > 1
    )
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[darshaningale]

In Power query groupby the selected columns and add count column.

You can find out rows having count >1

Regards
DI

Did I answer your question? Mark my post as a solution, this will help others!
Kudos are also welcome.