Calculated Column Based on 4 Columns

[Anonymous]

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	New
O2	U5	Completed	15-Sep
O2	U6	In Progress	14-Sep
O2	U7	New
O3	U8	In Progress	14-Sep
O3	U9	New

I want to calculate a column called USER such that table looks like this,

office Name	user ID	User Status	user Activated on	USER
O1	U1	Completed	20-Sep	U3
O1	U2	In Progress	19-Sep	U3
O1	U3	Completed	18-Sep	U3
O1	U4	New		U3
O2	U5	Completed	15-Sep	U5
O2	U6	In Progress	14-Sep	U5
O2	U7	New		U5
O3	U8	In Progress	14-Sep
O3	U9	New

The User Column is nothing but the User ID in each office having user status as "Completed" and having the earliest user activated date.

For Example, for O1 --> U1 and U3 are in completed status, but U3 has activated earlier than U1 that is on 18th sept. So calculated column user should have value as U3.

For O2, the only user who has completed status is U5 so user will have U5

for O3, nobody has the completed status so it remains empty.

Please help me with DAX query

@Ashish_Mathur 

Thanks, 

Dharani

[Ashish_Mathur]

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.

[Greg_Deckler · Accepted solution]

@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])