WOW. This was the missing video, I was looking for. I did not know that one could change the direction to both to cross-filter, and this video showed me. I think I am not a total noob to PowerBI, but also, these little things are hard to get in the jungle of options, so anybody pointing me to such tips is highly appreciated. Thankgs Guy in a Cube!
What a great video! Not only have you shown when many to many relationships are most suitable and demonstrated how the same result was achieved before this feature was released, you have also encouraged why it is worth going the extra step to prevent such ambiguities. A separate video that actually demonstrates such an ambiguity and why it occurs could make it even more obvious, why it is worth going the extra step.
My question is, How'd he do to get the Final_State_Metrics table? Since both metrics Population and Crimes Per Capita come from two different tables and not necessarily both of them have the same Geography key values
Usful and very informative video. And since am working in the oil & gas company, Many to Many relationship is extremely needed especially if we are working on wells,Reservoirs & fields data..etc for our analysis. Much thanks
00:05 Explaining the many-to-many relationships feature in Power BI. 01:20 Many-to-many relationships in Power BI can lead to complexities in data modeling. 02:24 Establishing relationships in Power BI using a bridge table. 03:24 Establishing bi-directional relationships enhances filtering across multiple tables in Power BI. 04:27 Introducing many-to-many relationships in Power BI. 05:28 Managing many-to-many relationships in Power BI requires careful filtering. 06:39 Combine data tables in Power BI for simplified metrics analysis. 07:48 Using a consolidated data model simplifies Power BI reporting. Crafted by Merlin AI.
I just love how clear you explain everything. Your channel is the best… been able to optimize and find solutions here which I sometimes dont get in the MS community website. 💯 thank youuuu!
Great video man. If you do a good job modelling your data, you don't need to use a many to many relationship and only need to use one to many relationship. I never need to use many to many relationship because I spend a lot of time modelling my data.
PowerQuery and he concatenates that state and city as the GeographicKey in the metrics table and then likely references the original metrics tables and normalizes/dedupes the values then creates the relationship.
Try a DAX calculated table with the DAX functions UNION to create the union and the SUMMARIZE function to declare which fields you're bringing into the union. All the best.
Thank you for sharing a unique way of dealing with many to many relationship. However, I would like to seek your advice regarding the following case. What if we not have information of violent crimes per capita for half of the State? Joining both the table to form Final_State_Metrics will result in many null values in ViolentCrimesPerCapita, what is the best approach for this scenario, should I still combine both the fact table together? Thank you and look forward for your reply.
If there are more two keys which are same and unique in two tables which is the most efficient key to join them ? any video to clear my doubts is appreciated thankyou.
Thanks for the video and the tips you give. My question is, How'd you do to get the Final_State_Metrics table? Since both metrics Population and Crimes Per Capita come from two different tables and not necessarily both of them have the same Geography key values
Great video you've got here Patrick.. I have been looking for a way around this many to many relationship issues cos its not always advised to use. I have a project on ground and i am faced with this challenge. I hope this solves it. I shall return with reports. Once again,Thank you
It's still not quite clear Patrick of what sort of problems the bidirectional relationship could cause so it'd be really nice if you can do a video on it..
Hi Patrick, You mentioned in the video that you would show what you would do if you use a Bridge Table and one table is missing values. Please can you show? Thanks
Is it logical to merge the 2 tables? I merge almost all of my tables, but I rarely see that as being proposed, I am new to Power BI, so just wondering if I am missing something.
Hi Patrick - Great video about an aspect of PBI that confuses a lot of people! Thinking beyond the functionality of getting a M2M relationship to work for report creators... what are the performance implications of each option? My understanding is that when we use a bi-directional relationship, behind the scenes, the values of one of the tables get copied into the other (is that right?). I want to avoid my data model from getting too large and bloated, so which of the 3 options that you showed us (1. Bridge table with 2 x M2M relationships, 2. new M2M design, and 3. remodel data model to be specific to the visualizations) would be the most performant? My guess is that you'll say 'It depends...' ;-)
What if you have two cost tables. One table has these fields: Cost Center, Product, and $s, with multiple products per cost center... because each cost center may allocate across multiple products. The second table has these fields: Cost Center, Cost Elements (eg. Salaries, Supplies, Travel, IT cost, taxes, etc...), and $s. Multiple Cost Elements per Cost Center. And you have Product Managers who want to know their $s by Product by Cost Element ?
Hi, your explanation makes things simpler to understand. I have one ask, on dataset modelling if i have one dataset lets say global sales and i want to share the same dataset to country report creators with a requirement that they can view only their data while creating the dashboard for their management. Is it possible?
hey, nice video! I have some doubts about many to many, What if I have two fact tables, the first is for Sales, and the 2nd one is for Daily Call records, So I want to know how many calls they received per day, and from those calls, which ended up in a Sale? does that make sense? Will it work in the same way? or How I can combine both, without losing the details of each fact table?
I tried the many-to-many relationship connection between historical orders to a future orders table after the Sept update and my totals again are all wrong... I'm no sure why...
Hey Patrick, so am I correct in thinking that merging a table is more efficient then solely relying on any relationship to give you the ability to merge columns from two different tables?
I built several marketing data analysis reports where the end user wanted to see data periodically, so I joined tables on dates and all the tables had many to many relationship. Although it appears that my report is showing the right data but I've read many articles promoting against it. Is it normal to join on dates as many to many?
how can I maintain hierarchy between multiple role playing dimensions. for example: year from one distinct date table is used as slicer and if I select any year then it should filter countries in another slicer coming from another distinct table and selected country should filter relevant states coming from another distinct table. It is a case where multiple columns like year&country&state are concatenated to create unique key which is joined to another table with similar concat column but this is leading to many to many relationship. please help
I know this question is not related to the video, but I've posted on a few forums and no one is responding, so hoping you can help: Hi, My team and I are looking at switching reporting platforms and have been building concept pieces within Power BI, so far we have found everything and more, however, we are keen on making reporting not only dynamic from a user perspective but also from a production point of view. currently, we use SSRS and are able to have lookup tables with logic coded in and ready to be pulled through into multiple reports ensuring that where metrics are repeated any logic changes will be reflected across all reports at once. I've built a sample look-up table storing the DAX syntax needed for some of the measures used, then using the LOOKUP I'm looking to return the SUM() calculation string from the lookup table as a formula, however, it's pulling through as a string value. Is there a way of having this type of setup to make reporting more flexible between our warehouse and Power BI? (I guess what i'm looking for is something like Excels indirect function, using the string as an address) I hope this makes sense. Thanks
It sounds like you want to build dynamic DAX statements and then load them independently? Maybe you'd be better creating that in SSAS and using Power BI to link directly to that data cube?
My initial thoughts were the same as Geoff’s. It sounds like you are wanting some centralised control over the model. Check out SSAS tabular model or alternatively a live connection to a Power BI dataset. We’ve gone down the Power BI dataset path which IT maintains and governs, against which users can build and publish their own reports. This works a treat for us.
Hi I got an issue I have 4 excel files (a, b, c, d) ppl joining company at different times (a, b, c, d) that uploaded into PBI. However I manage to merge Query on all of them.. But when I do calculated column, on two file for example b and c... It doesn't give me the correct answer... However I know that I used outer left join on column A to join the rest of files (b, c, d)... Basically all files have name.. Now I want to see the attrition between the files.
Hey Patrick, if the database was updated and other states were inserted, will the bridge table get updated too? or should I update it manually each time there is an update to the database? Thanks
Most of the time I hear many to many is dangerous because of potential ambiguity in the model. Could you elaborate on different scenarios when they really make sense? Thank you. Great video
Hey Michael, so glad I found your channel. Subscribed and Shared! One thing I am having trouble with is dealing with 2 tables on 2 separate sheets inside a single excel file. In the web app I can't find a way to cross reference those tables. The real need I have is to create a slicer on a report that shows two different tables to filter the results to show only a particular project charters rows from those two separate tables. One table is time tracking and another is software cost tracking. I have spent 6 hours on it and I am just lost!
that's good but, I think you miss a step - how did you know that the third table have all values from both facts ? - you made merge and then remove duplicates this is crucial step , you created new aggregate table i don't follow you skipping steps
at the moment you can't publish a report that has many to many relationships because it becomes part of a composite model. You'll have to go with one of the workarounds mentioned in this video
No I'm not having this. 6:50 into a 8:46 long vid and you start to give us a solution to all the issues you've been describing, then you say "combine the data into a distinct list" WTF does that mean? I thought it was APPEND if you wanted to do that? This is not a good solution video, it was useful as a thinking exercise but no solution as such was offered
Nice video, and Nice story , thank you. and hope more videos/stories about many-to-many relation ship. Power BI is good for talk about many-to-many relation ship, because easy/quick/visualize 😄
Everyone in is beating around the bush. No engineer would now give you an actual Step-by-Step tutorial. Just general talk, a little bit from here, a little bit from there ...
This solution is very cheap, and the rest of the relationships with the other tables of the model? In the end it only solved one thing out of a thousand.
WOW. This was the missing video, I was looking for. I did not know that one could change the direction to both to cross-filter, and this video showed me. I think I am not a total noob to PowerBI, but also, these little things are hard to get in the jungle of options, so anybody pointing me to such tips is highly appreciated. Thankgs Guy in a Cube!
What a great video! Not only have you shown when many to many relationships are most suitable and demonstrated how the same result was achieved before this feature was released, you have also encouraged why it is worth going the extra step to prevent such ambiguities.
A separate video that actually demonstrates such an ambiguity and why it occurs could make it even more obvious, why it is worth going the extra step.
I agree, cleaning up the data and reorganizing can have a big impact on performance as an added benefit.
My question is, How'd he do to get the Final_State_Metrics table? Since both metrics Population and Crimes Per Capita come from two different tables and not necessarily both of them have the same Geography key values
Usful and very informative video.
And since am working in the oil & gas company, Many to Many relationship is extremely needed especially if we are working on wells,Reservoirs & fields data..etc for our analysis.
Much thanks
00:05 Explaining the many-to-many relationships feature in Power BI.
01:20 Many-to-many relationships in Power BI can lead to complexities in data modeling.
02:24 Establishing relationships in Power BI using a bridge table.
03:24 Establishing bi-directional relationships enhances filtering across multiple tables in Power BI.
04:27 Introducing many-to-many relationships in Power BI.
05:28 Managing many-to-many relationships in Power BI requires careful filtering.
06:39 Combine data tables in Power BI for simplified metrics analysis.
07:48 Using a consolidated data model simplifies Power BI reporting.
Crafted by Merlin AI.
I just love how clear you explain everything. Your channel is the best… been able to optimize and find solutions here which I sometimes dont get in the MS community website. 💯 thank youuuu!
Great video man. If you do a good job modelling your data, you don't need to use a many to many relationship and only need to use one to many relationship. I never need to use many to many relationship because I spend a lot of time modelling my data.
So how do you go about making that nice final dimension table you use in your slicers at the end?
Is there any answer to this quest?
PowerQuery and he concatenates that state and city as the GeographicKey in the metrics table and then likely references the original metrics tables and normalizes/dedupes the values then creates the relationship.
Try a DAX calculated table with the DAX functions UNION to create the union and the SUMMARIZE function to declare which fields you're bringing into the union. All the best.
High five Patrick! Just the way you explain new functionalities makes life so much simpler! Keep those interesting videos flowing! Thanks :)
Thank you for sharing a unique way of dealing with many to many relationship. However, I would like to seek your advice regarding the following case. What if we not have information of violent crimes per capita for half of the State? Joining both the table to form Final_State_Metrics will result in many null values in ViolentCrimesPerCapita, what is the best approach for this scenario, should I still combine both the fact table together? Thank you and look forward for your reply.
Hi Patrick,In Power BI we have option to do lookup from vertical to horizontal without transpose the column
Great video as usual ....just wondering how you made the final state metrics table putting both values from those two fact tables in single table.
Many to many shouldn't always be used but it can be extremely helpful when dealing with RLS management acess in my opinion
If there are more two keys which are same and unique in two tables which is the most efficient key to join them ? any video to clear my doubts is appreciated thankyou.
Thanks for the video and the tips you give. My question is, How'd you do to get the Final_State_Metrics table? Since both metrics Population and Crimes Per Capita come from two different tables and not necessarily both of them have the same Geography key values
Great video you've got here Patrick.. I have been looking for a way around this many to many relationship issues cos its not always advised to use. I have a project on ground and i am faced with this challenge. I hope this solves it. I shall return with reports.
Once again,Thank you
Can we get the PBIX file to understand how we transformed to Relationship (Final) from where we started? Thanks.
As always, nice, funny and informative video!!! Thank you very much!
Hi Patrick, I love your video, could you also explain how you did the last part; combing two tables with the same name?
How does the apply security filter in both directions work? Do I have to check it?
It's still not quite clear Patrick of what sort of problems the bidirectional relationship could cause so it'd be really nice if you can do a video on it..
Hi Patrick, You mentioned in the video that you would show what you would do if you use a Bridge Table and one table is missing values. Please can you show? Thanks
Hi Patrick...how do we decide when to make a “custom column” and when we need a “bridge table”. Love your work👌
Is it logical to merge the 2 tables? I merge almost all of my tables, but I rarely see that as being proposed, I am new to Power BI, so just wondering if I am missing something.
Hi Patrick - Great video about an aspect of PBI that confuses a lot of people! Thinking beyond the functionality of getting a M2M relationship to work for report creators... what are the performance implications of each option? My understanding is that when we use a bi-directional relationship, behind the scenes, the values of one of the tables get copied into the other (is that right?). I want to avoid my data model from getting too large and bloated, so which of the 3 options that you showed us (1. Bridge table with 2 x M2M relationships, 2. new M2M design, and 3. remodel data model to be specific to the visualizations) would be the most performant?
My guess is that you'll say 'It depends...' ;-)
Hello,
I like the last approach to clean up the model.
Many-to-Many would make me nervous about unexpected results.
What if you have two cost tables. One table has these fields: Cost Center, Product, and $s, with multiple products per cost center... because each cost center may allocate across multiple products. The second table has these fields: Cost Center, Cost Elements (eg. Salaries, Supplies, Travel, IT cost, taxes, etc...), and $s. Multiple Cost Elements per Cost Center. And you have Product Managers who want to know their $s by Product by Cost Element ?
Did Patrick post his example report workbook?
Hi, your explanation makes things simpler to understand.
I have one ask, on dataset modelling if i have one dataset lets say global sales and i want to share the same dataset to country report creators with a requirement that they can view only their data while creating the dashboard for their management. Is it possible?
hey, nice video! I have some doubts about many to many, What if I have two fact tables, the first is for Sales, and the 2nd one is for Daily Call records, So I want to know how many calls they received per day, and from those calls, which ended up in a Sale? does that make sense? Will it work in the same way? or How I can combine both, without losing the details of each fact table?
I tried the many-to-many relationship connection between historical orders to a future orders table after the Sept update and my totals again are all wrong... I'm no sure why...
Hey Patrick, so am I correct in thinking that merging a table is more efficient then solely relying on any relationship to give you the ability to merge columns from two different tables?
Did you mention it's part of composite models capabilities?
You helped solve a huge problem for me. Thank you very much
I built several marketing data analysis reports where the end user wanted to see data periodically, so I joined tables on dates and all the tables had many to many relationship. Although it appears that my report is showing the right data but I've read many articles promoting against it. Is it normal to join on dates as many to many?
how can I maintain hierarchy between multiple role playing dimensions.
for example:
year from one distinct date table is used as slicer and if I select any year then it should filter countries in another slicer coming from another distinct table and selected country should filter relevant states coming from another distinct table.
It is a case where multiple columns like year&country&state are concatenated to create unique key which is joined to another table with similar concat column but this is leading to many to many relationship.
please help
The problem I have with this is that the bridge table needs to be constantly updated. How can we just combine the two without causing issues?
Correct approach. Nice video Patrick
Is it possible to have a refresh button in report itself for refreshing the dataset value?
I know this question is not related to the video, but I've posted on a few forums and no one is responding, so hoping you can help:
Hi,
My team and I are looking at switching reporting platforms and have been building concept pieces within Power BI, so far we have found everything and more, however, we are keen on making reporting not only dynamic from a user perspective but also from a production point of view.
currently, we use SSRS and are able to have lookup tables with logic coded in and ready to be pulled through into multiple reports ensuring that where metrics are repeated any logic changes will be reflected across all reports at once.
I've built a sample look-up table storing the DAX syntax needed for some of the measures used, then using the LOOKUP I'm looking to return the SUM() calculation string from the lookup table as a formula, however, it's pulling through as a string value.
Is there a way of having this type of setup to make reporting more flexible between our warehouse and Power BI?
(I guess what i'm looking for is something like Excels indirect function, using the string as an address)
I hope this makes sense.
Thanks
It sounds like you want to build dynamic DAX statements and then load them independently? Maybe you'd be better creating that in SSAS and using Power BI to link directly to that data cube?
My initial thoughts were the same as Geoff’s. It sounds like you are wanting some centralised control over the model. Check out SSAS tabular model or alternatively a live connection to a Power BI dataset. We’ve gone down the Power BI dataset path which IT maintains and governs, against which users can build and publish their own reports. This works a treat for us.
Thank you Patrick... Your explanations are always on point.!!
Possible to buy the guyinacube t-shirt?
Just get a gamecube tshirt
Hi
I got an issue I have 4 excel files (a, b, c, d) ppl joining company at different times (a, b, c, d) that uploaded into PBI.
However I manage to merge Query on all of them.. But when I do calculated column, on two file for example b and c... It doesn't give me the correct answer... However I know that I used outer left join on column A to join the rest of files (b, c, d)... Basically all files have name.. Now I want to see the attrition between the files.
Thank you dear
Big thumbs up, thanks Patrick
How could you define that it's LEFT, RIGHT or INNER JOIN in a relation?
Have you appended both table
Very good video thank you
Hey Patrick, if the database was updated and other states were inserted, will the bridge table get updated too? or should I update it manually each time there is an update to the database?
Thanks
Use a DAX calculated table with the DISTINCT DAX function. Updates with the data model.
As usual one more great idea..thanks
Sir where to practice dataset.. Dataset is not there
Yet another great video. Kudos!
Appreciate that! Thanks for watching 👊
Most of the time I hear many to many is dangerous because of potential ambiguity in the model. Could you elaborate on different scenarios when they really make sense? Thank you. Great video
Thanks
Hey Michael, so glad I found your channel. Subscribed and Shared! One thing I am having trouble with is dealing with 2 tables on 2 separate sheets inside a single excel file. In the web app I can't find a way to cross reference those tables. The real need I have is to create a slicer on a report that shows two different tables to filter the results to show only a particular project charters rows from those two separate tables. One table is time tracking and another is software cost tracking. I have spent 6 hours on it and I am just lost!
Really helpful, Great Thanks
Awesome, Thanks for watching! 👊
Awesome tip Patrick
that's good but, I think you miss a step - how did you know that the third table have all values from both facts ? - you made merge and then remove duplicates this is crucial step , you created new aggregate table i don't follow you skipping steps
Thank You for explaining it!!!
Great video guyincub👏
Good thank you
That's exactly what I need to check! You da man!
Nice video but can't publish to service..😔
PowerBI portal video, please.
Many to many saves the day, but do not understand the next level, is there more references on how to structure the merged? Tables.
How to create dimension modeling
Poor video editing on the areas you screen grabs making it hard to follow.
i've tried many to many but the problem is i can't publish it.. anyone has the solution for it?
You have to wait for the Service to allow it. Not sure when that will happen. I think they are targeting 2018, but don't bet a project on it.
at the moment you can't publish a report that has many to many relationships because it becomes part of a composite model. You'll have to go with one of the workarounds mentioned in this video
No I'm not having this. 6:50 into a 8:46 long vid and you start to give us a solution to all the issues you've been describing, then you say "combine the data into a distinct list" WTF does that mean? I thought it was APPEND if you wanted to do that? This is not a good solution video, it was useful as a thinking exercise but no solution as such was offered
Nice
Nice video, and Nice story , thank you. and hope more videos/stories about many-to-many relation ship.
Power BI is good for talk about many-to-many relation ship, because easy/quick/visualize 😄
Character In the video It's great, I like it a lot $$
My measure totals are all wrong after I introduce a bridge table.
Cool video!
Thanks Celso! we had fun with this one.
Everyone in is beating around the bush. No engineer would now give you an actual Step-by-Step tutorial. Just general talk, a little bit from here, a little bit from there ...
This solution is very cheap, and the rest of the relationships with the other tables of the model? In the end it only solved one thing out of a thousand.
Shoulda said it.
waste of my 10 minutes.
And like in every single video - no one says why not, just don’t do it
Well, at the end i find the video pretty useless as there is ton of talking and no real explanation how to build such table