Search code examples
powerbidaxpowerquerym

Power BI Flag Duplicates

We have a problem where our source data can produce unexpected duplicates. I want to create a process that will alert us when duplicates exist in tables.

I've used the approach to add the id to a table, then add the id again and count the id, then filter the visual for count > 1, which does a nice job of flagging the records duplicated. However, I want to set up alerts, so I want to setup a calculation I can pin to a dashboard and set up and alert. Struggling with setting up the right calculation.

My data looks like this:

enter image description here

So I would expect to see the ID 3 counted as duplicated. Tried this but it errors out with "EARLIER/EARLIEST refers to an earlier row context which doesn't exist". 

Measure = IF(
          CALCULATE(
                 COUNTA(Table1[ID]),
                 FILTER(Table1, Table1[ID] = EARLIER(Table1[ID]))
           )>1, 
           0,
           1
     )

Thanks!

Solution

  • Your code works as a calculated column, but EARLIER doesn't work quite the same in a measure since there is no inherent row context. If you want a measure, then try swapping out EARLIER for MAX or another aggregating function.

    Measure = IF(
          CALCULATE(
                 COUNTA(Table1[ID]),
                 FILTER(Table1, Table1[ID] = MAX(Table1[ID]))
           )>1, 
           0,
           1
     )