Measure or calculated column based on values from another table

[bastik]

I have the model below and I want to calculate the capacity of each assignee based on the remaining days in a Sprint.

This would be basically Users[USER_DAILY_CAPACITY] * SPRINTS[DAYS_REMAINING_IN_SPRINT] but I can't achieve this in DAX. 

Ultimately I want to display the Original Effort vs Remaining effort vs Remaining capacity per assignee and filter with Sprints_Reports.SPRINT_ID . 

I tried merging USER with SPRINTS using some intermediary tables and I can calculate the Remaining capacity per user, but with no direct relationship between SPRINTS and ISSUES doesn't bring the correct data if I filter with the Sprints_Reports.SPRINT_ID . 

Any ideas?

[Anonymous]

Hi @bastik ,

A good model is half the battle, and Power BI prefers a star schema model.

Understand star schema and the importance for Power BI

Could you please provide a file with no sensitive data to be used for testing? This would be extremely helpful for the next step.

How to provide sample data in the Power BI Forum

Other blogs that may be useful:

Many-to-many relationship guidance

Avoiding circular dependency errors in DAX

Using USERELATIONSHIP in DAX

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data