Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
moia79
Helper I
Helper I

Sum a column based on conditional criteria on two other tables

dear PowerBI masters,

 

I have a problem with a database, based on multiple tables. The data structure is as follows:

 

table 1 contains the list of "methods" and the respective "time labor", "time machine" and "grouping"

Metodshour laborhour machinegrouping
ABC1252
DEF2466
GHI1198

 

table 2 associates the methods to each product ID. One product ID is linked to one or more methods.

Product IDMethod
111ABC
111DEF
222DEF
222GHI
333ABC
333DEF
333GHI
444DEF

 

table 3 contains instead the quantities, by Product ID, month, year and classification. Little complexity, the same ID can have multiple rows with same classification, year and month.

Product IDClassificationYearMonthQuantity
111

 

I would need a resulting table that, for each "Method" in table 1 is able to sum the "Quantity" in table 3 - maintaining the classification, year and month - for all items that are linked to that "Method" in table 2, i.e.

 

Metodshour laborhour machinegroupingProduct IDClassificationYearMonthQuantity
ABC1252111
ABC1252333    
DEF2466111    
DEF2466222    
DEF2466444    
GHI1198222    
GHI1198333    

 

not sure what is the best way to do it. Can you please suggest? Should I create a new table combining the existing tables?

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @moia79 

 

You can establish the following relationship between 3 tables.

vzhangti_0-1668582756799.png

Result:

vzhangti_1-1668582797129.png

Is this the result you expect? If not, give examples of your desired outcome.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @moia79 

 

You can establish the following relationship between 3 tables.

vzhangti_0-1668582756799.png

Result:

vzhangti_1-1668582797129.png

Is this the result you expect? If not, give examples of your desired outcome.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I created the relationships, but PBI allows me only to create multiple-to-multiple relationships.

 

I managed it to create two additional tables, by using the function "merge", and even if the results is a very long table, it works.

 

thanks

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors