Is MANY TO MANY, too many? | Power BI many to many relationships explained

hello and good afternoon this is Harrison Bergeron calm and in today's video we are going to talk about many to many relationships we're actually going to do three things number one I'm going to show you what a many-to-many relationship is number two I'm going to show you why you should avoid many so many relationships and number three I'm going to show you how to avoid many to many relationships so this is a disclaimer I am no expert in many-to-many relationships okay what I am an expert on is avoiding them so just did you know okay if you have any comments questions suggestions you know anything there is to know about many to many comment box will love to hear it but yeah let's begin the video chit chat at the end all so let's do it so first of all let's explain what a many-to-many relationship is I have here a tea table this is perhaps the most common case you have a sales table with actual sales and then you have a budget table we budgeted the sales okay and you won of course to measure how you do it against our budget nothing we are there so what makes these a many-to-many relationship is like if you want to join these two tables you will see that both some date and on product do you have duplicate so if we look at for example let's pick another color pink is good if we look at cheese you can find cheese in many places in both tables so that would be a many it's actually written many to n because n is the number of iterations which is indefinite and if we look at the speed if we look at the data table you will see that there are also many to many so this is the first of January 2018 there are two records the same is in here so there is a typical many-to-many is perhaps not a true many-to-many but if you would like to join these tables together you will get like this you will get a many-to-many relationship okay so what we're going to do now is we're going to go to power bi and try these with a many-to-many relationship and see what happens okay so let's go so now we are in power bi and we have a sales table a budget table and they are exactly the same as I show you so there you have them sales and budget and before if we wanted to join these things together power bi would type mm-hmm you're not allowed they say I don't remember exactly what it says but maybe this a minute relationship and you had to do something else to fix this and we will go through it anyhow would you need to do in case you don't want to work with many-to-many which you shouldn't so now it is possible to have a to July update many to many relationships so if you actually try to join this product product you see the many is the asterisk too many then you will create a directional relationship by default but you can change that to single if you want to okay so okay I say Oh fantastic now we can do many too many joints well yes but huge but ok let's do this we have date oh my god this is so annoying where is it just a second there you go I hate this hierarchical dates it just drives me nuts so we pick date from sales we pick product and we pick sales ok so this is how did the sales they will look like nothing strange there put it somewhere there and then let's put just for reference put the three things there put it as a table put it as a table and have it there so we know what records we have and now we are going to say we're going to pick the date from the sales date we're gonna pick the product we're going to pick the sales and then let's pick we have a relationship now right so let's pick the budget don't see what it does and this is what things get interesting with many to many you often we get wrong results and let's you know what you're doing okay so this is our budget table if we do like this product and budget do you see the numbers that show up 79 and 81 is what is showing here if you remember we had a relationship from product to product so what if this is the witness some in the products for ignoring the date and just going Y with the product and saying hey 79 79 and this date is like if it's not there basically so what happens if we change these relationship and say oh we take date let's do that what is he doing now okay I'm gonna show you recognize the numbers now 22 68 and 70 so now he's ignoring the product category and he's actually giving us the budget for dates only so if I would let's copy this if I remove the product from the from the table it will actually give us that yes it's true the budget for the date in the first of January 22 68 and 70 great right and for sales is also just demonstrate it we put sales there and we have 70 70 140 so this is actually working and then you can start doing things but if you add the products filter there if we ignore it completely okay and it will give you the wrong results so let me give you another example if you watch my video yesterday I actually explained all talked about composite models that was also introduced in the July 2000 Indian update and we had two tables we have country code that it was a this was an excel file and then daily subs was a direct query and now with composite models you can join this day was but you will get a many-to-many relationship forced on you you can't change that there's just no way that you can do it the only way to do it is many-to-many and it just selected by itself it's a single where daily subs filter country codes okay this that's what it did by default and as you can see is of course not filtering correctly because what we want is exactly the opposite right we one country we want to hide country code from here and we want country to filter daily subs let's hide a - okay and if we change these to a both directional it will filter it correctly but if we don't it won't okay so this is another example of how things get really tricky with many-to-many and i have another example so here we have another example this is a video I made a while ago I call it relationship with duplicate values and this is basically a many-to-many model so what we have here is this is the way to solve many to many without having many to many relationships listen ignore that for a second we'll talk about it later and we have here a manufacturing table this is a list of products there are being manufactured by day so we know how many products we have manufacture by day if we go here to manufacturing table we will see it and then we have a stock table and here we have the list of products are in stock and there are more products in stock that we manufacture so we basically sell things that we don't produce and let me show you that very quickly so this is the manufacturing table this is the stock and as you can see only cyclecross bikes and mountain bikes are common on both tables so now that we can do many to many relationships how about we give it a go so we book product ID product ID this was not possible to do before you will create a many-to-many bidirectional also so now we get product name from the stock table and we want to know how many of our stock products we manufacture so we go there two amount of manufactured products and you see it filters correctly filters only the product name the products that we have both in stock and in the manufacturing table which is those two Oh some money give us the amount of manufacturing manufacture products which is 47 and 26 that is correct but look at the total the total is not correct and the reason for that I have a video on that is basically that totals is not the sum of these it's just filtering both tables in the background I mean this case is not passing any filter so for the total is giving us the entire manufacturing table okay there is a way to fix these and I have a video on that I'll post it there so you can go and check it out so this problem we do have also with the intermediate table I'll show you what that is in a second but it just wouldn't make you aware of another reason you'll have when working with mini it so many there I found at least okay okay so let's continue with this case let's say that we have a budget we have a sales we manage the many to many relationships and we want to create you know a real functioning model one of the things you will need is a calendar so let's build a calendar in this case our sales table has continues date so if I pick this thing values he will my calendar you need to build a calendar okay I'm just going to be a bit lazy and do we like this calendar this thing and then but it doesn't matter it both has the same date but let's bake sale states so this is going to give me a calendar can I pick a format that I actually can read okay whatever like that so this is our calendar and now we said okay we won't go in date there and date there and what is happening is forcing us to an inactive relationship okay and the reason for that is to create and be within the model what are you talking about I have a video that of course to go and check that out but it basically power bi doesn't know which way to go to filter the tables so it makes one inactive so you have to activate it with tax which is a pain you know things start to get complicated I mean it's completely not okay so how do we solve this here's my advice is stay away from many to many as they were the plague seriously no let's delete this let's make this purely active and now we have one too many want many okay so the next thing you need to do is to have a product because we had too many to many relationships so we go here a new table product this is our the one that will have unique values let's set the sales of the unique values for products and there you go there you go there we go product and we go product and now we have products from the product table okay date from the date table but different budget table and sales from the sales table and look at that okay let's see if that was correct so we had ten twelve twenty three let's order by date ten twelve twenty three four five yeses gorgeous product sales also giving us the right results okay so that is the way you should continue work and even if you have many to many relationships available to you and while we're at it let's do some best practice mark your calendar as a date and hide the columns that you don't want your users to be using or seen so the way this you see this is just if I want a date I have to go to the calendar if I want a product I go to the product and then sale some budget calls from sales and budget so it's like super easy for a business user to utilize your models okay that is the point of the cell service I don't think many too many ourselves are visco together so we stay away basically so there are here are the things that will happens with many too many let me summarize this what is going to happen is that this kind of relationships create complex data sets which either do not return the correct results use your excessive computing because you know is filtering many too many number of records that renews the filter and join and stuff is just insane if your tables are being and sometimes you won't even return any results so what do you think about many to many relationships trickier okay I really really wish that it wasn't that easy to create many to many relationships for a self-service business intelligence tool you know for business users it just makes no sense that you can actually create them like that but hopefully Microsoft will because he there entails that a little bit in the meantime you've seen this video stay away from many to many relationships unless you know what you're doing so do it the traditional way create your bridge tables and go with that okay that's the best advice that I can give you so it is power which that means one video a day and tomorrow we're going to talk about these dual storage things that they release a little I update so stay tuned for that I'll see it work but

Channel: Curbal

Views: 43,448

Keywords: power, curbal, many to many relationships power bi, Is MANY TO MANY too many?, power bi video tutorial, Power BI many to many relationships explained, many to many relationship in database, many to many relationship, powerbi, Power bi, cubal, Curbal, excel bi, Curbal.com, bi, power bi desktop, power bi designer, excel

Id: NdrrjkvH2zo

Length: 17min 18sec (1038 seconds)

Published: Wed Aug 22 2018