Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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, @Serdet
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 @Serdet ,
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, @Serdet
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.