Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: join tables vs relation between tables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

andresp_g

New Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2019
05:18 AM

Hi everyone,

let me explain what I'm trying to achieve. I have a table with my inventory information like this

Date | Product | Color | Value |

1 | 1 | 1 | 500 |

1 | 1 | 2 | 300 |

1 | 2 | 1 | 400 |

1 | 2 | 2 | 250 |

Also I have the information about how this product should be allocated by client like this

Date | Product | Client | Allocation |

1 | 1 | 1 | 30% |

1 | 1 | 2 | 70% |

1 | 2 | 1 | 50% |

1 | 2 | 2 | 50% |

The expected result is each client knows in units how many they have. The solution right now is to combine the two tables using a left join and multiply units per Allocation, but it significally increase the model size. Is there a way to relate this two tables, and create a measure instead?

Thank you so much for your help

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

d_gosbell

Super User III

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-24-2019
10:47 PM

Xue's formula has a flaw in it at the total level, because it is filtering 'Table' on MAX(Table1[ProductID] ) at the grand total level the max is equal to 2. So the SUM(Value) returns 650 and the SUM(Table1[Allocation]) is not filtered at all as the filter is applied to the other table, so it returns the sum of all Allocations which is 200%. So 650 x 200% = 1300

For logic like this you would be better to use a SUMX pattern to loop over one of the tables row by row. (I'm also filtering on both date and product as per your original requirement)

Allocated Value = SUMX( 'Table 1', Var _date = 'Table 1'[Date] var _product = 'Table 1'[Product] return 'Table 1'[Allocation] * CALCULATE( SUM('Table'[Value]), 'Table'[Date] = _date, 'Table'[Product] = _product) )

4 REPLIES 4

Highlighted
##

v-xuding-msft

Community Support

Re: join tables vs relation between tables

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2019
09:23 PM

Hi @andresp_g ,

I created a measure that you can have a try.

Measure = CALCULATE(SUM('Table'[Value])*SUM('Table 1'[Allocation]),FILTER(ALLEXCEPT('Table','Table'[Color]),'Table'[Product] = MAX('Table 1'[Product])))

If it is not what you want, please share your expected results.

Best Regards,

Xue Ding

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

Best Regards,

Xue Ding

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

Xue Ding

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

Highlighted
##

andresp_g

New Member

Re: join tables vs relation between tables

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2019
10:50 AM

Hi @v-xuding-msft , thank you for your reply!!

this is the expected result by line, however the total units doesnt seem to be equal, do you know how to fix it? Also I have the next questions for you:

- since there will be several products and days, how should be the relation between the two tables, should be a combined key made by date & product? or tables are not related?

- if the inventory table would have other columns as the color column, should be added in the filter parameter?

Once again thank you so much

v-xuding-msft

Community Support

Re: join tables vs relation between tables

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-24-2019
06:26 PM

Hi @andresp_g ,*>this is the expected result by line, however the total units doesnt seem to be equal, do you know how to fix it?*

For the sample data, the date is always 1. It can't be shown as a continuous line chart. Suggest you to try the measure and add the columns into Line chart based on your actual scenario. And which column do you mean for total unites?

*>since there will be several products and days, how should be the relation between the two tables, should be a combined key made by date & product? or tables are not related?*

For my sample, there is no relationship between the tables. You could try it firstly without relationship and check if it is what you want. And if there is any other calculation in your report, maybe you should create it.

*>if the inventory table would have other columns as the color column, should be added in the filter parameter?*

If you just what the result of the measure, you don't need filter. If you want to view the result of different color, you could add a color slicer to filter them.

(I don't understand your actual report, so probably can't reply you acurately. )

Best Regards,

Xue Ding

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

Best Regards,

Xue Ding

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

Xue Ding

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

Highlighted

d_gosbell

Super User III

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-24-2019
10:47 PM

Xue's formula has a flaw in it at the total level, because it is filtering 'Table' on MAX(Table1[ProductID] ) at the grand total level the max is equal to 2. So the SUM(Value) returns 650 and the SUM(Table1[Allocation]) is not filtered at all as the filter is applied to the other table, so it returns the sum of all Allocations which is 200%. So 650 x 200% = 1300

For logic like this you would be better to use a SUMX pattern to loop over one of the tables row by row. (I'm also filtering on both date and product as per your original requirement)

Allocated Value = SUMX( 'Table 1', Var _date = 'Table 1'[Date] var _product = 'Table 1'[Product] return 'Table 1'[Allocation] * CALCULATE( SUM('Table'[Value]), 'Table'[Date] = _date, 'Table'[Product] = _product) )

Announcements

Visit our Community Blog for articles, guides, and information created by fellow community members.

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors

User | Count |
---|---|

328 | |

198 | |

114 | |

72 | |

62 |

Top Kudoed Authors

User | Count |
---|---|

369 | |

272 | |

153 | |

81 | |

79 |