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