Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Add a column from another table based on two criteria

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 a...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
3 years ago

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