Forum Discussion

neek05's avatar
neek05
Frequent Visitor
3 years ago
Solved

Return group with max date

Hi, I have a table like the one below:

 

USERIDDATEGROUP
User120/01/2023GroupA
User121/01/2023GroupA
User122/01/2023GroupB
User123/01/2023GroupB
User218/01/2023GroupC
User220/01/2023GroupD
User221/01/2023GroupD
User222/01/2023GroupC

 

And I want to get the group that holds the value with the max date,  so I should have a table like this: 

USERIDDATEGROUP
User122/01/2023GroupB
User123/01/2023GroupB
User218/01/2023GroupC
User222/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!

 

Need Help
  • vicky_'s avatar
    vicky_
    3 years ago

    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

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    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.