Forum Discussion
Helper IIICalculation based on multiple columns in related table
I am trying to create a resource table for a project that monitors the progress of tasks and assigns a value to the progress of the task, based on 2 criteria. I can't figure out how to get the fact table (the table with the current details on the task) to look up the information on the resource table and calculate the value. The intent is that, when the values need to change for future tasks and statuses, we can just edit the resource table and not update any formulas or calculations. The fact table is updated 3 times/day with the new tasks and their states.
How do I create a calculated column to give me to points in the fact table based on the combinations in the resource table? We have a dozen tasks, with 8 possible statuses, but I've presented a simplified example below
Examples:
Fact Table:
| Task_Name | Status | Value (this is the calculated value) |
| Admission Note | Not Started | 0 |
| Admission Note | Pending | 2 |
| Visit Note | Not Started | 0 |
| Visit Note | Complete | 2 |
Resource table with point values:
| Task_Name | Status | Points |
| Admission Note | Not Started | 0 |
| Admission Note | Pending | 2 |
| Admission Note | Complete | 4 |
| Visit Note | Not Started | 0 |
| Visit Note | Pending | 1 |
| Visit Note | Complete | 2 |
option 1 :
calculated column ==
lookupvalue (
table_2 [ points] ,
table_2[Task_name], table_1[Task_name],
table_2[Status], table_1[Status]
)
option 2 :
cc =
maxx (filter (table_2 , table_2[Task_name] = table_1[Task_name] && table_2[Status] = table_1[Status])
let me know if it works for you .
If my answer helped sort things out for you, feel free to give it a thumbs up and mark it as the solution! It makes a difference and might help someone else too. Thanks for spreading the good vibes! 👍🤠
4 Replies
- Daniel29195
Community Champion
option 1 :
calculated column ==
lookupvalue (
table_2 [ points] ,
table_2[Task_name], table_1[Task_name],
table_2[Status], table_1[Status]
)
option 2 :
cc =
maxx (filter (table_2 , table_2[Task_name] = table_1[Task_name] && table_2[Status] = table_1[Status])
let me know if it works for you .
If my answer helped sort things out for you, feel free to give it a thumbs up and mark it as the solution! It makes a difference and might help someone else too. Thanks for spreading the good vibes! 👍🤠
aflintdepmJust to make sure I understand and test correctly:
In my fact table, I would add a column with the formula, and "Table_2" refers to the resource table?
- Daniel29195
sorry didnt notice the table names
the is correct.
fact table == table_1
Resource table with point values == table_2
you creat the column in table _ 1
- Ashish_Mathur
Super User
Hi,
Write this calculated column formula in the first table
Points = calculate(sum(RT[Points]),filter(RT,RT[Task_Name]=earlier(FT[Task_Name])&&RT[Status]=earlier(FT[Status])))
Hope this helps.
