dax query to locate duplicates

[jeannier2022]

I need to write a DAX query to locate duplicate growers I want to create a column in a table called duplicate growers.

I have a column GrowerID, Grower. The issue I am having is that the users enter duplicate grower id for grower names, the names are a little different the user puts comma between the words or a - between words but the same growerid which is causing a duplicate error in my dashboard. I would like to put in a dax query to locate duplicates and based on the values yes for dulicates no for no duplicates use these values in Power Automate to trigger an action to send an email to myself if there are duplicate errors so I can fix those errors before the board members view the dashboard.

the code that I have so far:

Grower_Duplicates = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'MAIN/PRIMARY LUT'[GROWER] ),
        FILTER (
            'MAIN/PRIMARY LUT',
              'MAIN/PRIMARY LUT'[GRADE ID] = EARLIER ( 'MAIN/PRIMARY LUT'[GROWER ID] )
                && 'MAIN/PRIMARY LUT'[GROWER] IN { "Monitored", "Unmonitored" }

        )
    )
RETURN
    IF ( _count > 1, "yes", "no" )

[v-nuoc-msft · Accepted solution]

Hi @jeannier2022

@Jihwan_Kim Good share!

For your question, here is the method I provided:

Here's some dummy data

"MAIN/PRIMARY LUT"

Create a measure. This formula creates a local variable to store the of the current row and another variable to normalize the name by removing commas and hyphens. It then uses these variables within a function that counts the number of rows with the same and normalized name. If more than one row is found, it flags as "yes" for duplicates, otherwise "no" for unique entries.

Grower_Duplicates = 
    VAR CurrentGrowerID = 
        SELECTEDVALUE('MAIN/PRIMARY LUT'[Grade ID])
    VAR _Grower = 
        SUBSTITUTE(
            SUBSTITUTE(
                SELECTEDVALUE('MAIN/PRIMARY LUT'[Grower])
                , 
                ",", 
                ""
            ), 
                "-", 
                ""
        )
RETURN IF (
        CALCULATE (
            COUNTROWS ( 'MAIN/PRIMARY LUT' ),
            FILTER (
                ALL ( 'MAIN/PRIMARY LUT' ),
                'MAIN/PRIMARY LUT'[Grade ID] = CurrentGrowerID &&
                SUBSTITUTE(SUBSTITUTE('MAIN/PRIMARY LUT'[Grower], ",", ""), "-", "") = _Grower
            )
        ) > 1,
        "yes",
        "no"
    )

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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

[Jihwan_Kim]

Hi,

Please try below whether it suits your requirement.

Grower_Duplicates =
VAR _countcondition =
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                'MAIN/PRIMARY LUT',
                'MAIN/PRIMARY LUT'[GRADE ID] = EARLIER ( 'MAIN/PRIMARY LUT'[GROWER ID] )
                    && 'MAIN/PRIMARY LUT'[GROWER] IN { "Monitored", "Unmonitored" }
            ),
            'MAIN/PRIMARY LUT'[GROWER]
        )
    ) > 1
RETURN
    IF ( _countcondition, "yes", "no" )