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

mangaus1111's avatar
mangaus1111
Icon for Solution Sage rankSolution Sage
3 years ago

Hi Anonymous ,

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

  • 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