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

IDTask IDID 2
12999

 

Dataset 2

Task IDHours
220

 

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?

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

4 Replies

  • 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

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

    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.