Filter by Dups to Perform Analysis

[ConnieMaldonado]

I have a table that includes the following:

Last_Register	Email	No	Version	Status	Account	Last_Trip	EE	Count Emails	Remove
11/15/2022 13:42	person1@gmail.com	12345678	1	not avail	12345		123	2	FALSE
3/9/2023 7:09	person1@gmail.com	12345678	2	registered	23456	3/9/2023 11:39	123	2	FALSE
2/11/2023 13:05	person2@gmail.com	23456789	1	not avail	34567	2/11/2023 17:45	345	3	FALSE
3/9/2023 7:28	person2@gmail.com	23456789	1	registered	45678	3/9/2023 11:59	345	3	FALSE
3/9/2023 7:30	person2@gmail.com	34567890	2	registered	56789		345	3	FALSE
3/7/2023 16:40	person3@gmail.com	45678901	1	pending	67890	3/7/2023 21:16	678	2	FALSE
3/9/2023 7:26	person3@gmail.com	45678901	1	registered	78901		678	2	FALSE

I am trying to create a "Remove" filter to determine whether the record should be removed from the data.

So first I need to identify dup records based on email.  Then, based on certain criteria involving No, status and registered date, I need to build logic to determine which record(s) to remove.  I created a "Remove" column which is FALSE for now (until I build the logic).

I was able to identify records with duplicate emails by creating the following column:

Count Emails =

Var Emails = Table[Email]

RETURN

CALCULATE(

    COUNTROWS(Table),

    all(Table),

    Table[Email] = Emails

)

I have no idea where to begin to isolate a "set" of dups and determine which to remove.

For example, for a set of "dups", let's say person1@gmail.com, I want to keep the record with the latest "Last_Register" date and remove the others.  So I would set Remove = TRUE for the first record with Last_Register = 11/15/2022 13:42. 

Here's what the results would look like:

Last_Register	Email	No	Version	Status	Account	Last_Trip	Employee No	Count Emails	Remove
11/15/2022 13:42	person1@gmail.com	12345678	1	not avail	12345		123	2	TRUE
3/9/2023 7:09	person1@gmail.com	12345678	2	registered	23456	3/9/2023 11:39	123	2	FALSE
2/11/2023 13:05	person2@gmail.com	23456789	1	not avail	34567	2/11/2023 17:45	345	3	TRUE
3/9/2023 7:28	person2@gmail.com	23456789	1	registered	45678	3/9/2023 11:59	345	3	TRUE
3/9/2023 7:30	person2@gmail.com	34567890	2	registered	56789		345	3	FALSE
3/7/2023 16:40	person3@gmail.com	45678901	1	pending	67890	3/7/2023 21:16	678	2	TRUE
3/9/2023 7:26	person3@gmail.com	45678901	1	registered	78901		678	2	FALSE

How would I build that logic - i.e., to set Remove = "TRUE" for record(s) with the earlier "Last_Register" date for dups based on email.  If I can build that logic, I can figure out the rest.  Just not sure where to start.

Any help would be appreciated.  Thank you.

[CNENFRNL · Accepted solution]