cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

1 REPLY 1
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.

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors