cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Serdet
Post Patron
Post Patron

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?

1 ACCEPTED 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

View solution in original post

4 REPLIES 4
mangaus1111
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors