Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.