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 logged lines. 

The Dax query should check if there are more entries with the same date and number of logged lines, and if so, flag the line with a true/false marking.

 

How would we go about formulating such an query?

 

Inputs are much appreciated.

 

Good day.

 

/David

  • 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 ()
)

16 Replies

  • 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 ()
)