Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
aflintdepm
Helper III
Helper III

Calculation 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_NameStatusValue (this is the calculated value)
Admission NoteNot Started0
Admission NotePending2
Visit NoteNot Started0
Visit NoteComplete2

 

Resource table with point values:

Task_NameStatusPoints
Admission NoteNot Started0
Admission NotePending2
Admission NoteComplete4
Visit NoteNot Started0
Visit NotePending1
Visit NoteComplete2
1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

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

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Daniel29195
Super User
Super User

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

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?

@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 

 

 

 

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.