Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have the below datasets.
Datset 1
ID | Task ID | ID 2 |
1 | 2 | 999 |
Dataset 2
Task ID | Hours |
2 | 20 |
Dataset 3
ID 2 |
999 |
Relationships are as follows and need to be this way for the data to show correctly.
Dataset 1 - Dataset 2 (many to many on task ID)
Dataset 1 - 3 (many to one on ID 2)
This allows me to show a table that correctly displays
'ID' and 'Hours' together.
For further DAX anaylsis I need column 'Hours' to be added to Dataset 1. I have tried merging but this only allows for one table filter and I have tried the RELATED function but this does not work on a many to many relationship.
Any ideas how I can make this work?
Solved! Go to Solution.
Hi, @Anonymous
Your sample data is too small to give me a clear understanding of your data model.
The RELATED function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. It doesn't support many-to-many relationship.
Please try functions like 'Sum',"Max","Average" to aggregate the results.
Column = CALCULATE(SUM('Dataset 2'[Hours]),FILTER('Dataset 2','Dataset 2'[Task ID]='Dataset 1'[Task ID]))
Best Regards,
Community Support Team _ Eason
I think that if you Summarize Dataset 2, then you can use LOOKUPVALUE function.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
which granularity has Dataset 2? Can you show me more rows as sample?
Hi,
Thanks for your response. Dataset two includes another column called 'Site' which needs to be filtered in order for the hours to be displayed correctly.
Hi, @Anonymous
Your sample data is too small to give me a clear understanding of your data model.
The RELATED function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. It doesn't support many-to-many relationship.
Please try functions like 'Sum',"Max","Average" to aggregate the results.
Column = CALCULATE(SUM('Dataset 2'[Hours]),FILTER('Dataset 2','Dataset 2'[Task ID]='Dataset 1'[Task ID]))
Best Regards,
Community Support Team _ Eason
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
69 | |
68 | |
40 | |
29 | |
26 |
User | Count |
---|---|
88 | |
49 | |
45 | |
38 | |
37 |