Remove Rows based on date

[Anonymous]

I can't find anything exactly like what I'm trying to accomplish, so I need a bit of help.

I have a data set where I have some problem children that i need to remove from the data set entirely.

I need to keep the most recent date for each status and ID.

here's what they look like:

ID1	Status1	Date1
ID1	Status1	Date2
ID1	Status1	Date3
ID1	Status2	Date4
ID1	Status2	Date5
ID1	Status2	Date6
ID2	Status1	Date7
ID2	Status1	Date8
ID2	Status1	Date9
ID2	Status2	Date10
ID2	Status2	Date11

Desired State:

ID1	Status1	Date3
ID1	Status2	Date6
ID2	Status1	Date9
ID2	Status2	Date11

Thanks!

[az38 · Accepted solution]

Hi @Anonymous 

create new calculated table

Table 2 = 
FILTER(
ADDCOLUMNS('Table', "Rank", RANKX(FILTER('Table', 'Table'[Status]=EARLIER('Table'[Status]) && 'Table'[ID]=EARLIER('Table'[ID])),'Table'[Date],,DESC)),
[Rank]=1
)

[Anonymous]

Nailed it.  You're my new hero.

😁

[Anonymous]

Also,

anyone with the same problem...

You'll need to recreate in the created table any calculated columns (and I'm assuming measures) that you had already created in the original table.  otherwise you may have bad data if you were pulling from one of the rows that got removed.