Forum Discussion
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 | 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
Thanks,
Dharani
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])
2 Replies
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.
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])
