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


     

     

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