Forum Discussion
Measure from two tables
I have one table containing the names of different tasks and a field called Window that define a number of days. Table is call Tasks and has three elements, Task, Job, Window.
A second table identifies the people who have worked on each task and the last time they performed work on that task. Table is called AssignedPeople and has elements Person, Task, LastDateWorked
I want to be able to calculate what percent of the people have worked on each task since Today - window.
I will also want to be able to calculate the same thing, but a job level (tasks are assigned to jobs).
I can't figure out how to do this, Your help would be most appreciated.
11 Replies
GarnseyG , you should able to join both tables on task and should able to calculate.
For more help Can you share sample data and sample output.
Not sure how to share my pbix file, or for that matter how to attach anything
If this helps: Here is some sample data as an example
Task Table:
Task Project Window a p1 12 b p1 20 c p1 15 d p2 35 e p2 42 f p3 18 g p3 8 h p3 23 i p3 21 j p4 14
People table
Person Task LastDateWorked 1 a 5/9/2020 1 b 4/24/2020 1 c 5/3/2020 1 d 5/9/2020 1 e 5/7/2020 2 f 4/24/2020 2 g 5/4/2020 2 h 4/29/2020 2 i 5/18/2020 2 j 5/19/2020 3 a 5/4/2020 3 b 5/9/2020 4 c 5/9/2020 4 d 4/21/2020 4 e 5/18/2020 4 f 5/10/2020 4 g 5/7/2020 5 h 4/21/2020 5 i 5/5/2020 5 j 5/11/2020 So for task "A" with a 12 day window. Using 5/18/2020 as today, 12 days ago is 5/6. Person 1 worked 5/9 so he/she is inside the window (good) while person 3 is outside the window (Bad). Two people work, one good, so 50% for that task.
I would use the same logic when doing it by project, or doing it by person.
Hi, GarnseyG
I'd like to suggest you create a measure as below.
Percenatge By task = var _tabperson = SUMMARIZE( People, People[Person], People[Task], People[LastDateWorked], "Window", var _task = People[Task] return LOOKUPVALUE(Task[Window],Task[Task],_task) ) var _newtab = ADDCOLUMNS( _tabperson, "flag", IF( TODAY()-[Window]<=[LastDateWorked], 1,0 ) ) var numofgood = SUMX( FILTER( _newtab, [flag]=1 ), [flag] ) var result = numofgood/DISTINCTCOUNT(People[Person]) return IF( ISBLANK(result), 0, result )Today is 5/21/2020. Here is the result.
The pbix is attached in the end.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Whow, most impressive! It's going to take some time to understand what you've done here, but the calculations look right at the task level. I'm going to see if I can figure out what you did and apply it to do it at the project level.
I'll let you know.
