Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm trying to create a report that tracks employees' tasks they've completed and the role they completed it as.
For example, all employees have a role at the beginning of this year, but some may be promoted. These changes are tracked in the Employees table:
UserID Name Role Date 1 EmployeeA Support 1/1/2018 2 EmployeeB Support 1/1/2018 3 EmployeeC Support 1/1/2018 4 EmployeeD Support 1/1/2018 5 EmployeeE Senior Support 1/1/2018 3 EmployeeC Senior Support 3/18/2018 5 EmployeeE Supervisor 6/4/2018
The tasks they've completed over the year so far are in the Tasks table:
TaskName Date UserID Task A 2/1/2018 2 Task B 2/1/2018 1 Task C 3/1/2018 3 Task D 3/25/2018 3 Task E 5/5/2018 4 Task F 6/3/2018 5 Task G 6/4/2018 5
What I was thinking the output would be is similar to this:
TaskName Date UserID Role Task A 2/1/2018 2 Support Task B 2/1/2018 1 Support Task C 3/1/2018 3 Support Task D 3/25/2018 3 Senior Support Task E 5/5/2018 4 Support Task F 6/3/2018 5 Senior Support Task G 6/4/2018 5 Supervisor
I can sort of achieve this with LOOKUPVALUE and UserID, but I'm having difficulties adding the date part (unless there's a more efficient way to go about doing this?).
Thanks in advance!
Solved! Go to Solution.
Hi,
Write this calculated column formula
=LOOKUPVALUE(Employees[Role],Employees[UserID],[UserID],Employees[Date],CALCULATE(MAX(Employees[Date]),FILTER(Employees,Employees[UserID]=EARLIER([UserID])&&Employees[Date]<=EARLIER(Tasks[Date]))))
Hope this helps.
Hi,
Write this calculated column formula
=LOOKUPVALUE(Employees[Role],Employees[UserID],[UserID],Employees[Date],CALCULATE(MAX(Employees[Date]),FILTER(Employees,Employees[UserID]=EARLIER([UserID])&&Employees[Date]<=EARLIER(Tasks[Date]))))
Hope this helps.
Thank you both! I used Ashish_Mathur's suggested formula and it worked perfectly.
Hey @st-mb
A couple of steps to calculate this. First, I create a table of unique UserID's to connect the Employees and Tasks table.
Then you can calculate the new role column with the following formula:
Role = VAR CurrentUser = FIRSTNONBLANK(Tasks[UserID],1) RETURN CALCULATE( FIRSTNONBLANK(Employees[Role],1), TOPN( 1, FILTER( Employees, Employees[UserID] = CurrentUser && Tasks[Date] >= Employees[Date] ), Employees[Date] ) )
This gives you the desired output, shown below:
Let me know if you have any questions on the formula.
Hope this helps!
Parker
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |