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 o...
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.

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.