Forum Discussion

Dbjerring's avatar
Dbjerring
Regular Visitor
9 years ago
Solved

Remove duplicates based on values from multiple cells

Hi Community,   We have dataset based on logs - here we would like to create a new column with a true/false value if the line is a duplicate. We have UserID, LogTimeDate, LogTime, and number of lo...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    Dbjerring,

     

    You could modify the formula as shown below.

    IsDuplicate =
IF (
    COUNTROWS (
        FILTER (
            Logs,
            Logs[LogCNumber] = EARLIER ( Logs[LogCNumber] )
                && Logs[LogTime.1] = EARLIER ( Logs[LogTime.1] )
                && Logs[LogID] < EARLIER ( Logs[LogID] )
        )
    )
        > 0,
    TRUE (),
    FALSE ()
)
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Dbjerring

 

Are you aware of query editor? I think you can use this tool to reach your goal.

 

Best,

Martin

  • Dbjerring's avatar
    Dbjerring
    Regular Visitor
    9 years ago

    Hi Anonymous,

     

    Yes, but I'm not sure how to perform an advanced query like this - any tips?

     

    /David

    • v-chuncz-msft's avatar
      v-chuncz-msft
      Community Support
      9 years ago

      Dbjerring,

       

      You may use the following DAX to add a calculated column.

      IsDuplicate =
IF (
    COUNTROWS (
        FILTER (
            Table1,
            Table1[UserID] = EARLIER ( Table1[UserID] )
                && Table1[LogTimeDate] = EARLIER ( Table1[LogTimeDate] )
        )
    )
        > 1,
    TRUE ()
)
      • Dbjerring's avatar
        Dbjerring
        Regular Visitor
        9 years ago

        v-chuncz-msft

         

        The DAX expression works, but for some reason both lines (also the the one without EARLIER value) gets tagged as TRUE.

        So all lines with a possible duplicate gets TRUE VALUE.

         

        Any thoughts?

         

        The adjusted DAX calc I use:

         

        IsDuplicate =
        IF (
        COUNTROWS (
        FILTER (
        Logs;
        Logs[LogCNumber] = EARLIER ( Logs[LogCNumber] )
        && Logs[LogTime.1] = EARLIER( Logs[LogTime.1] )
        )
        )
        > 1;
        True();FALSE()
        )

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Dbjerring

       

      You go to the menu on the left where there is 3 buttons, and choose the one called "Data". You then right-click your data and choose "Edit query". Then you right-click the column-header of the column where you want to remove duplicates and choose "Remove duplicates". Let me know if this solves your problem.

       

      I'll also gladly elaborate.

      • Dbjerring's avatar
        Dbjerring
        Regular Visitor
        9 years ago

        Anonymous - I don't need to remove them, I have to tag them. And since it's based on values from two different cells, the remove duplicate function isn't enough. Thank you for your elaboration though.

         

        Good day to you sir