DAX/power query removing duplicates by pk column and max date

[Anonymous]

Hello,

I have the dataset as mentioned below and I want to remove duplicates based on a column and max date. 

input set:

order_id posted_date
12734 5/29/2019
12735 4/16/2019
12734 4/15/2019
12735 4/20/2019
12735 3/18/2019

desired output
order_id posted_date
12374 5/29/2019
12375 4/20/2019

Thanks in advance!

[AlB · Accepted solution]

Hi @Anonymous 

Create a new calculated table:

NewTable =
FILTER (
    Table1,
    Table1[posted_date]
        = CALCULATE (
            MAX ( Table1[posted_date] ),
            ALLEXCEPT ( Table1, Table1[order_id] )
        )
)

[AlB · Accepted solution]

Hi @Anonymous 

Create a new calculated table:

NewTable =
FILTER (
    Table1,
    Table1[posted_date]
        = CALCULATE (
            MAX ( Table1[posted_date] ),
            ALLEXCEPT ( Table1, Table1[order_id] )
        )
)

[Watsky]

This fixed my issue I was having too! Thanks for this!

[Anonymous]

@AlB ,

Thanks for the response.

There are other columns in the table. I want to filter the duplicates based on order_id and posted_date. Can't it be done from power query or from DAX on the same table?

Thanks

[Anonymous · Accepted solution]

I am using the following check calculated column in order to solve the problem and a page level filter which accepts only 1's.

Check =

VAR _DATEFILTER = MAX('table'[post_date])

VAR _LASTDATE = CALCULATE(

MAX('table'[post_date]),

ALLEXCEPT(

table,

'table'[order_id]

),

'table'[post_date] <= _DATEFILTER

)

RETURN IF(LASTNONBLANK('table'[post_date], 1) = _LASTDATE, 1, 0)