Forum Discussion

tonijj's avatar
tonijj
Helper IV
4 years ago
Solved

Identify Duplicates - multiple parameters

Hi,

 

Been searching the forum but haven’t really found a solution to my problem. Some threads are close, but maybe not all the way.

 

What I want to achieve:

Find and list duplicates based on 3 different columns

 

The columns that should be analyzed for duplicates:

  • Supplier 
  • Category
  • Purchasing Unit

 

 

If either 2 or 3 parameters are the same, they should be listed as duplicates.

 

I have created a unique ID per row in the Query.

 

As it is for a company with lot of different divisions, (lots) the Purchasing units can all buy from the same supplier. However, at times, each Division creates a supplier record for the same Supplier, hence creating a duplicate.

 

And/or – Division A categories the Supplier as “Phone retailer” and Division B categories the same supplier as “Computer manufacturer”, same thing there, two records, same Supplier.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi tonijj ,

    • I am very sorry that I wrote the wrong formula. Please correct it.
    Measure =
VAR _countsupplier =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Supplier] = SELECTEDVALUE ( 'Table'[Supplier] )
        )
    )
VAR _countcategory =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Category] = SELECTEDVALUE ( 'Table'[Category] )
        )
    )
VAR _purchasingubit =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Purchasing Ubit] = SELECTEDVALUE ( 'Table'[Purchasing Ubit] )
        )
    )
RETURN
    IF (
        ( _countsupplier >= 2
            && _countcategory >= 2 )
            || ( _countsupplier >= 2
            && _purchasingubit >= 2 )
            || ( _countcategory >= 2
            && _purchasingubit >= 2 ),
        "Duplicate",
        "No"
    )

    Because you are looking for   2 or 3 parameters are the same. We only need to consider the simplest two with duplicate values between them.

    • Yes, You can write a formula like mine. But too many parameters can affect the performance of the formula. Please pay attention to.

     

    Best Regards

    Community Support Team _ Polly

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi tonijj ,

    Please have a try.

    Create a measure.

    Measure =
VAR _countsupplier =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Supplier] = SELECTEDVALUE ( 'Table'[Supplier] )
        )
    )
VAR _countcategory =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Category] = SELECTEDVALUE ( 'Table'[Category] )
        )
    )
VAR _purchasingubit =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Purchasing Ubit] = SELECTEDVALUE ( 'Table'[Purchasing Ubit] )
        )
    )
RETURN
    IF (
        ( _countsupplier >= 2
            && _countcategory >= 2 )
            || ( _countsupplier >= 2
            && _purchasingubit >= 2 )
            || ( _countsupplier >= 2
            && _purchasingubit >= 2 ),
        "Duplicate",
        "No"
    )

    If I have misunderstood your meaning, please provide your desired output with more details and you sample pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

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

     

    • tonijj's avatar
      tonijj
      Helper IV
      4 years ago

      Hi Anonymous 

      First of all, a big thanks for this! 

      I have just a few quick follow-up questions;

      1, If we look at the bottom part of the formula, isnt the red highlighted part redundant? 

      ( _countsupplier >= 2

                  && _purchasingubit >= 2 )

                  || ( _countsupplier >= 2

                  && _purchasingubit >= 2 ),

        

      2. Can I have more parameters to identify duplicates, basically, can I include more columns simply by following the logic in the code you provided?

       

      Sincerely

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi tonijj ,

        • I am very sorry that I wrote the wrong formula. Please correct it.
        Measure =
VAR _countsupplier =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Supplier] = SELECTEDVALUE ( 'Table'[Supplier] )
        )
    )
VAR _countcategory =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Category] = SELECTEDVALUE ( 'Table'[Category] )
        )
    )
VAR _purchasingubit =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[supplier number] = SELECTEDVALUE ( 'Table'[supplier number] )
                && 'Table'[Purchasing Ubit] = SELECTEDVALUE ( 'Table'[Purchasing Ubit] )
        )
    )
RETURN
    IF (
        ( _countsupplier >= 2
            && _countcategory >= 2 )
            || ( _countsupplier >= 2
            && _purchasingubit >= 2 )
            || ( _countcategory >= 2
            && _purchasingubit >= 2 ),
        "Duplicate",
        "No"
    )

        Because you are looking for   2 or 3 parameters are the same. We only need to consider the simplest two with duplicate values between them.

        • Yes, You can write a formula like mine. But too many parameters can affect the performance of the formula. Please pay attention to.

         

        Best Regards

        Community Support Team _ Polly

         

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