Forum Discussion
Create calculated column from several connected tables
I have a Task and Resource table, which are connected using a common TasktoResource table. I have another ResourceValue table and would like to sum the values from that to the Task table.
Task
| TID 1 |
| TID 2 |
| TID 3 |
| TID 4 |
Resource
| RID 1 |
| RID 2 |
| RID 3 |
RID 4 |
TasktoResource
| TID 1 | RID 1 |
| TID 1 | RID 2 |
| TID 2 | RID 3 |
| TID 2 | RID 4 |
ResourceValue
| RID 1 | 10 |
| RID 2 | 20 |
| RID 3 | 30 |
| RID 4 | 40 |
I am hoping to create a NewTable with a calculated column having the following results (sum of ResourceValue per Task) -
NewTable
| TID 1 | 30 |
| TID 2 | 70 |
| TID 3 | |
| TID 4 |
Not sure which relationships I should establish for this NewTable either. Appreciate any advice on achieving this. I tried it in PQ using Merge tables, and don't think it's very efficient as it's taking a long time to process. The actual data will have 10k+ rows of Tasks and 100k+ rows of Resourcevalue.
based on your relationships you should be able to summarize into a new table.
NewTable =SUMMARIZE(Task,Task[TaskID],"Value", sum(ResourceValue[Value]))see attached.
4 Replies
what does your relationship view look like of these tables?
based on your relationships you should be able to summarize into a new table.
NewTable =SUMMARIZE(Task,Task[TaskID],"Value", sum(ResourceValue[Value]))see attached.This is exactly what I needed. Thank you for taking the time to assist with this!