Looking at Power BI Many to Many

yo what's up its Patrick I'm going to Q in this video we're talking about many to many relationships in power bi stay tuned okay meeting too many relationships so why people will ask me when Microsoft released this when the power bi team released meaning - meaning the feature into power bi desktop back in July 2008 teams like why did they do it and then some people were like yes finally they did it and so and they go Patrick what do you think I go I use it where I need okay and so got a lot of comments and emails can you kind of explain why they did this and so that's why I'm doing this video okay so instead of all this talking you guys know how I like to do what is head over to my laptop okay so what you see here is I have two tables I have one table that contains you know states and cities and another one that contains states and cities one is focusing on filing crimes per capita and one is focus focusing on population and I like to analyze across the two right and so I want to establish a relationship just between the state abbreviations that's all I want to do and if you try to do this prior to the to rely update you will get an error saying hey you need to have a distinct list of column a distinct list of values for your key column on one side of the relationship right now I have a many-to-many relationship because one state abbreviation in the crimes table will be related to many states in the abbreviate in the population table and then one abbreviation in the population table will be related to many abbreviations in the crime table so now I have a meaning to many relationship well she didn't work she just couldn't do it right you'd get that error and say you got to go fix this and so a lot of us struggle and they were like oh boy why can't they just let us do many-to-many relationships okay there's a reason you know for from a data model or I pride myself on data modeling and one thing I wouldn't do is something like this unless I just absolutely had to because it can introduce some ambiguities and produce questions by my end users that just don't want to answer okay and I'll explain you know towards the end but anyway back to this so the workaround is to introduce a third table and so some people go Apache that's a bridge table or that's a cross-reference table and so you can call them whatever you want for me I'm you know our database is kind of my thing and so this is not a true bridge table it's just a table to help me resolve my meeting too many relationships go look up what a bridge table is and you'll see what I'm talking about if you don't know most you guys probably already know but anyway back here so I have my third table and my third table is just composed of a distinct list of states which is just a combination of all the states here I just combined all I've got a complete list of the states between the two tables and then I just distinct it out I just removed all the duplicates okay and then what that allows me to do is because prior to July 2018 I couldn't establish a minute too many relationship and so now using that I can establish a one-to-many relationship between you know the crime table and the population table and then I can use that middle table if I want to filter across the two but there's a problem because there's additional attributes like city state name and city name that's not available and so if I went here and tried to do anything you'll see how nothing's working across the two tables right but if I choose something here right in the filters so this middle table so if I wanted to use city name or state name for any type of filtering or anything like that I would have to make both of these relationships or whichever one I wanted to use bi-directional right so I will change the cross filter direction on both of these relationship to both and so what what happen is yeah yeah I know I know I know what would happen is once I establish that then I can go back to my report and if I choose Georgia it'll filter all the way through if I choose Louisiana well it breaks that's because Louisiana doesn't exist in both tables okay so that's what happens when you do that and then if I go on the other side I start choosing things it works but if I choose something that don't exist on the other side like Mississippi the other table blinks out okay this can get kind of confusing to those individuals that are building reports based on this model because they don't know which column to use or anything like that and like I said towards the end of the video I'll show you I would do in this case right it may not work out in every case but in this case I'll show what I would do to kind of resolve this okay so then July 2018 of they came and Microsoft introduced many too many so basically just a copy of those other two tables without the third table and now what I get to do I'm gonna show you I'm gonna show you actually what happens here okay so I'm gonna delete this relationship right let's delete it and so basically it's the same scenario that I had before meaning too many between the two state abbreviations if I establish that relationship watch this I'm gonna go ahead and establish this relationship the property window is gonna open up is gonna say hey you're about to establish a meeting to manage your relationship the cardinality of this relationship right it's many to many I'm just letting you know not saying you can't do it I'm just letting you know okay so let's accept it and notice that it makes a cross filter direction both right which is very important so I'll go ahead and click OK I don't need the third table anymore there's my relationship with the cross filter direction set to vote I'll go back to my report go to my mini to mini tab and now notice if I click Georgia it filters over here if I click you know Illinois it filters over there it works great right it cross filters it cross filters both the tables based on my selection the challenge is now I've introduced common values of grits different columns in different tables so if I'm an in user reporting against this which one do I which city do I use which state do I use you know which state abbreviations do I use it can be a convoluted mess right they just can't confuse those I'm not gonna see it right maybe I'll see I'm not like I said alright confuse people and so my recommendation right many two meeting is great if there's no workaround do it just be very careful right be very careful if you can hide some things if you can kind of provide your end users with direction on which columns or you know values columns to use across the two tables you just make their life easier if you can write if you can you can take a different approach which is my last very last tab in this report and what I would do is and for you date of mileage like I know what you're born with this Patrick what you can do is take all the columns across all the common data in both of those tables you know state up in my case state abbreviation state name and city name combine them into a distinct list one single table get your metrics if you can impossibly one table and we'll kind of look like this this relationship here will be a one-to-many relationship one state can be related to many of the metric values cuz I don't know how many cities are gonna have which associated some multiple states you can create a key column like I've done hide some things and now when you give this report into an in user they just use one table so if you go here I'm gonna go to my feel this and check it out so I have a single table but all my geographer can say okay let me I'm gonna use this state as a slicer so I'll make this as my slicer like I'm nice to see is one little bitty table we make this my slicer so I have a complete what are you doing here we go do too much work so I'll make this my slicer and bring my state name there alright so there's my state name as my slicer I'm gonna make this a little larger so you guys can see it and then give my city's alright make that a bar chart and then add population or something for my metrics table white without guess in or anything right I clearly know what's going on I don't have to figure out which table to select which where do I pull this column from which table should I pull this column I just know because I have a single place that contains all that consolidated data right not saying that many - meaning does not have use cases and scenarios where it's applicable it's just that if you can take them take the next step right easy be efficient like me all right take the next step and kind of clean your model up ok what do you guys think comments questions criticism post them in the comments below if this is your first time visiting the guy in the cube channel you know what to do hit that subscribe button and if you like my video big thumbs up as always from Adam and myself thanks for watching and we'll see you in the next video

Channel: Guy in a Cube

Views: 91,557

Rating: 4.9368687 out of 5

Keywords: power bi many to many, power bi many to many join, power bi many to many relation, power bi many to many relationships, many to many power bi, many to many relationship, power bi, power bi desktop, power bi desktop for beginners, power bi desktop tutorial for beginners, power bi desktop update - july 2018, power bi tutorial for beginners, power bi video tutorial

Id: ZrANsDNnZug

Length: 8min 46sec (526 seconds)

Published: Wed Sep 19 2018