Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated Column Based on 4 Columns

Hello,   I have the following table,   office Name user ID User Status user Activated on O1 U1 Completed 20-Sep O1 U2 In Progress 19-Sep O1 U3 Completed 18-Sep O1 U4 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Maybe:

    USER Column =
  VAR __Office = [office Name]
  VAR __Table = FILTER('Table',[office Name] = __Office && [User Status] = "Completed")
  VAR __Min = MINX(__Table,[User Activated on])
RETURN
  MAXX(FILTER(__Table,[User Activated On] = __Min),[user])
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

This calculated column formula works

=LOOKUPVALUE(Data[user ID],Data[user Activated on],CALCULATE(MIN(Data[user Activated on]),FILTER(Data,Data[office Name]=EARLIER(Data[office Name])&&Data[User Status]="Completed")),Data[office Name],Data[office Name])

Hope this helps.