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 :- 100,2345,4,so i need to create a measure which gives me count of records = 2 Audit IDActivity IDActivity RC Cycle10023454100234541002346410023474100234841000456751000456852006543120065431Output Audit IDActivity IDActivity RC CycleMeasure100234541200654311Total 2

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

Count Duplicate records with multiple columns

6 Replies

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
  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 👍👍 !!!!!
RanjeetK
Helper I
3 years ago
@Mikelytics , Thanks for your efforts
but it does not work , i have tried it in my data as you can see
Measure 2 = SUMX(
VALUES(data[Activity ID]),
Var Var_All = CALCULATE(
COUNTROWS(data),
ALLEXCEPT(data,data[Activity ID])
)
Return
IF(Var_All>1,Var_All-1,BLANK()
))
It returns Blank
Please share your file if possible and thanks for immediate reply
- Mikelytics
  Resident Rockstar
  3 years ago
  Hi Ranjeet,
  
  I checked again and please follow the steps below.
  
  1) this is my starting table with redundant line-items
  
  2) Make sure that all columns are text
  
  3) Create a key column for your duplication tracking with the formula:
  [Audit ID] & "_" & [Activity ID] & "_" &[Activity RC Cycle]
  
  1st result
  
  now load the table and create the following measure
  
  CycleMeasure = SUMX( VALUES('Test Table'[KeyColumn]), Var Var_All = CALCULATE( COUNTROWS('Test Table'), ALLEXCEPT('Test Table','Test Table'[KeyColumn]) ) Return IF(Var_All>1,Var_All-1,BLANK() ) )
  
  now it should work 🙂
  
  Best regards
  Michael
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
  - RanjeetK
    Helper I
    3 years ago
    Hey Hi Mikelytics,
    Ohhh Nice trick Mike !!! this could be the solution but..........
    Yes it works in Import and Direct query mode but it does not work in Live Connection where we can use Virtual approach only, we can only create measures not a calculated column.
    if possiable can you please suggest me how to do that? or anyone have such example please
RanjeetK
Helper I
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