Count Duplicate records with multiple columns

[RanjeetK]

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 ID	Activity ID	Activity RC Cycle
100	2345	4
100	2345	4
100	2346	4
100	2347	4
100	2348	4
1000	4567	5
1000	4568	5
200	6543	1
200	6543	1

Output 

Audit ID	Activity ID	Activity RC Cycle	Measure
100	2345	4	1
200	6543	1	1
Total			2

[RanjeetK · Accepted solution]

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

[RanjeetK]

@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]

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]

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

[Shaurya]

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]

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 👍👍 !!!!!