Forum Discussion

RanjeetK's avatar
RanjeetK
Helper I
3 years ago
Solved

Count Duplicate records with multiple columns

Hello , i need help to solve following problem. i need count of Audit ID which are having duplicate records. Like every audit ID have a different Activity key but some of them are repeated  EX :- 1...
Need Help
  • RanjeetK's avatar
    RanjeetK
    3 years ago

    Hey Hi All ,
    I got solution 

    Var _Count = ADDCOLUMNS(SUMMARIZE(data,data[Audit ID],data[Activity ID],data[Activity RC Cycle]),
    "@Count",CALCULATE(COUNTA(data[Activity RC Cycle]),ALLSELECTED(data[Audit ID]))
    )

    Var _Greaterthan = FILTER(_Count,[@Count]>=2)
    Var _Result = COUNTROWS(_Greaterthan)
    RETURN
    _Result


     

     

Shaurya's avatar
Shaurya
Memorable Member
3 years ago

Hi RanjeetK,

 

Use the following formula to identify the records that are duplicates:

 

Count = SUMX('Table',IF('Table'[Audit ID]=EARLIER('Table'[Audit ID])&&'Table'[Activity ID]=EARLIER('Table'[Activity ID])&&'Table'[Activity RC]=EARLIER('Table'[Activity RC]),1))

 

Then summarize the original table after filtering out the non-duplicate rows:

 

Duplicates = SUMMARIZE(FILTER('Table','Table'[Count]>1),'Table'[Audit ID],'Table'[Activity ID],'Table'[Activity RC],"Measure",1)

 

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts

  • RanjeetK's avatar
    RanjeetK
    Helper I
    3 years ago

    Hi Shaurya,
    this will work for calculated column ,
    Actually i am using Live connection so i can't create a calculated Column but yes we can do it in Virtual Table approach ( create View similar as in SQL and then use it)
    thanks for your Efforts Bro 👍👍 !!!!!