How to filter the latest record in Power BI?

[FkZ0]

Hi Community,

I have a database of information that has multiple records for the same User ID , I'd like to filter the information in a way that my final table only contains the latest record ( the record with the most recent date) per user ID. How would you approach this?

Thanks in advance team !

[Etero · Accepted solution]

It depends. If you want to do it with dax, then something like this - 

(Let's assume your table is named Sale and it contains columns such as Customer ID, Date and Price).

LatestDate =

SUMMARIZE(

    'Sales',

    'Sales'[Customer ID],

    "LatestDate", MAXX(FILTER('Sales','Sales'[Customer ID]=EARLIER(Sales[Customer ID])),'Sales'[Date]),

    "Price", CALCULATE(MAX('Sales'[Price]),FILTER('Sales','Sales'[Date]=MAX('Sales'[Date])))

)

use this dax to create a new table.

but if it's possible, you can use Power Query (I would prefer this one)

1. Group by Customer ID and choose max operation in date

2. If you can use some M code to bring the price too, if not u can merge tables(the original data with the New Table (Grouped by Customer ID)

[saudansari · Accepted solution]

This should help 

Case Rank =

RANKX(

    FILTER(

        YOUR TABLE NAME,

        YOUR TABLE NAME[USERID] = EARLIER(SUPPORT__CW_OPS_VOLUME_PARTNER_CARE[USERID])

    ),

    YOUR TABLE NAME[DATE_COLUMN],

    ,

    ASC

)

[Etero · Accepted solution]

It depends. If you want to do it with dax, then something like this - 

(Let's assume your table is named Sale and it contains columns such as Customer ID, Date and Price).

LatestDate =

SUMMARIZE(

    'Sales',

    'Sales'[Customer ID],

    "LatestDate", MAXX(FILTER('Sales','Sales'[Customer ID]=EARLIER(Sales[Customer ID])),'Sales'[Date]),

    "Price", CALCULATE(MAX('Sales'[Price]),FILTER('Sales','Sales'[Date]=MAX('Sales'[Date])))

)

use this dax to create a new table.

but if it's possible, you can use Power Query (I would prefer this one)

1. Group by Customer ID and choose max operation in date

2. If you can use some M code to bring the price too, if not u can merge tables(the original data with the New Table (Grouped by Customer ID)

[Fowmy]

@FkZ0 

Please clarify if you mean the latest record to be loaded to Power BI or do you need to show the latest record in a table visual?