Add a column from another table based on two criteria

[Anonymous]

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?

[mangaus1111]

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.

[mangaus1111]

Hi @Anonymous ,

which granularity has Dataset 2? Can you show me more rows as sample?

[Anonymous]

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. 

[v-easonf-msft · Accepted 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