The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
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.
This works well !
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.
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |