How to get the latest date for each user ID based on a a filter from another table

[HomerSimpson123]

How to get the latest date for each user ID based on a a filter from another table

For E.g on the table visual i have the below..

The User ID and Date Entered fields are in one table (table1), but the Value is in another table (table2)

User ID (Table1)                  DateOrdered (Table1)                   Item (table2)
1                                         16/06/2023                                   Pen
1                                         15/04/2023                                   Pen
2                                         02/08/2023                                   Book
2                                         03/08/2023                                   Book

And i only want the most recent entry displayed so it would look like the below

User ID (Table1)                 Date Ordered (Table1)                    Item (table2)
1                                        16/06/2023                                     Pen
2                                         03/08/2023                                    Book

I want to be able to then create a measure to count the most recent date the pen was ordered, and again the most recent date a book was ordered for each User ID.

I've tried Calculate Max(Date) and latestDate measures  including using related table to link the 2nd table but i'm not getting anywhere and instead all that does is give me a really long list of rows with today's date or the latest date that was entered for every row.

I just want to be able to see on the table the latest date for each user they ordered a pen or a book for each user in the row, instead of seeing a row showing every date a pen or book was ordered for the same user. Simply just want the latest date in Item (table 2) for each user.  

Then do a count of the number of users in the table visual based on the latest date an order was made for each user in the table.

So something like 

Latest Date for each user when a Item = Pen or Item = Book for ordered.

Any help would be appreciated.

Thanks in advanced.

[v-shex-msft · Accepted solution]

HI @HomerSimpson123,

How did these table relationship mapping, can you please share some more detail information about these?

How to Get Your Question Answered Quickly  

In addition, you can also try to use the following measure formula if it works on you side: (use on visual level filter to filter 'Y' flag)

formula =
VAR mDate =
    CALCULATE (
        MAX ( Table1[DateOrdered] ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[User ID] ),
        VALUES ( Table2[Item] )
    )
VAR currDate =
    MAX ( Table1[DateOrdered] )
RETURN
    IF ( currDate = mDate, "Y", "N" )

Regards,

Xiaoxin Sheng