Filtering Virtual Table to Display Latest Purchase for Each User Using DAX

[baby_cabbage]

Hello, I’m new here.

I’ve created a virtual table called TableA using CALCULATETABLE. It contains two columns: User ID and Date of Purchase. There can be multiple rows for the same User ID since each user can make purchases on different dates.

I want to filter TableA so that it only keeps the rows with the most recent Date of Purchase for each unique User ID. In other words, I need a result where each User ID appears only once, showing their latest purchase.

How can I achieve this using only DAX? I’ve found several articles online, but they all use Power Query to transform the table first. Since my table is virtual, I’d like to avoid using Power Query to modify the original data.

Thanks!

[v-linyulu-msft · Accepted solution]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @baby_cabbage 

Regarding the issue you raised, my solution is as follows:

1.My sample data is as follows:

2.As I am unsure how you have constructed the CALCULATETABLE(), I will assume that I only need the entries with IDs 1 and 2:

TableA=CALCULATETABLE(SELECTCOLUMNS(
        'Table',
        "User_ID1", 'Table'[User ID],
        "Date_of_Purchase1", 'Table'[Date of Purchase]
    ),'Table'[User ID] IN {1,2})

3.Here is my calculated table:

Table2 = 
VAR TableA =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table',
            "User_ID1", 'Table'[User ID],
            "Date_of_Purchase1", 'Table'[Date of Purchase]
        ),
        'Table'[User ID] IN { 1, 2 }
    )
VAR LatestPurchases =
    SUMMARIZE (
        TableA,
        [User_ID1],
        "Latest Purchase Date", MAX ( 'Table'[Date of Purchase] )
    )
RETURN
    LatestPurchases

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[baby_cabbage]

This works well !

[lbendlin]

Needs to be a two step process. First step to identify the most recent date, second step to filter by the date matching the result.