Calculation based on multiple columns in related table

[aflintdepm]

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

[Ashish_Mathur]

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.

[Daniel29195 · Accepted solution]

@aflintdepm 

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! 👍🤠

[aflintdepm]

Just 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]

@aflintdepm 

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