Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Find out duplicate values from composite key

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

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

2 Replies

  • darshaningale's avatar
    darshaningale
    Icon for Resolver II rankResolver II
    4 years ago

    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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.