cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Return group with max date

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!

1 ACCEPTED SOLUTION
Super User

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.

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors