Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi, I have a table like the one below:
USERID | DATE | GROUP |
User1 | 20/01/2023 | GroupA |
User1 | 21/01/2023 | GroupA |
User1 | 22/01/2023 | GroupB |
User1 | 23/01/2023 | GroupB |
User2 | 18/01/2023 | GroupC |
User2 | 20/01/2023 | GroupD |
User2 | 21/01/2023 | GroupD |
User2 | 22/01/2023 | GroupC |
And I want to get the group that holds the value with the max date, so I should have a table like this:
USERID | DATE | GROUP |
User1 | 22/01/2023 | GroupB |
User1 | 23/01/2023 | GroupB |
User2 | 18/01/2023 | GroupC |
User2 | 22/01/2023 | GroupC |
Just to clarify, I don't need the rows with the max date by group, I need the rows with the group that has the max date. Does anyone know of a way I could accomplish this? Ideally, I would like to get a calculated column in Dax or a measure to use as a filter in a table visual (power query isn't going to work for me). Thanks in advance!
Solved! Go to Solution.
I think there's two parts to the formula - find which group has the max date (var groupWithMaxDate), and then check if the existing group is equal to that group (the return IF condition).
Column =
var groupWithMaxDate = CALCULATE(MAX('Table'[GROUP]),
FILTER('Table', 'Table'[DATE] = CALCULATE(MAX('Table'[DATE]), ALLEXCEPT('Table', 'Table'[USERID]))
&& 'Table'[USERID] = EARLIER('Table'[USERID])
))
return IF('Table'[GROUP] = groupWithMaxDate, 'Table'[GROUP])
Hopefully this helps.
I think there's two parts to the formula - find which group has the max date (var groupWithMaxDate), and then check if the existing group is equal to that group (the return IF condition).
Column =
var groupWithMaxDate = CALCULATE(MAX('Table'[GROUP]),
FILTER('Table', 'Table'[DATE] = CALCULATE(MAX('Table'[DATE]), ALLEXCEPT('Table', 'Table'[USERID]))
&& 'Table'[USERID] = EARLIER('Table'[USERID])
))
return IF('Table'[GROUP] = groupWithMaxDate, 'Table'[GROUP])
Hopefully this helps.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
75 | |
72 | |
69 | |
45 | |
41 |
User | Count |
---|---|
63 | |
41 | |
30 | |
28 | |
28 |