Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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!

  • Anonymous's avatar
    Anonymous
    1 year ago

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

    Hi, Anonymous 

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

    Hi, Anonymous 

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      This works well !

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    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.