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


     

     

Mikelytics's avatar
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's avatar
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