#41 Understanding relationships in Power BI Desktop

hello and good evening this is Ruth pezuela from Kerrville dot-com and today we're going to talk about relationships but don't worry we're not going to talk about mine we're going to talk about power bi is relationships so if you have been working with power bi for a while I'm sure you've already done some relationships or you let power bi do some relationships for you but in this video we are actually going to do a deep dive on relationships and what all the options are how they work and how you can avoid some issues that make home if you configure the right relationships wrong so without any more delay let's start okay so here we have our demo file we will go through it in a second but we have two tables we have a manufacturing table and we have a product a de Loup cap table lookup table what means is the data is fairly stable it doesn't change by days so you have it for example a product table unless you create a new product it will stay the same through time and the reason for creating relationships between these two is that you can actually use data from here to analyze data in the manufacturing table and you could of course merge the tables together but if you do that you will have a performance you will get performance problems if your data is big so let's see what happens first when you don't have relationships I'm sure you've seen these case before so let's for example have trade a table with the product name from the manufacturing table no sorry we get product name from that product table our lookup table and then we get a amount from the manufacturing table let's do a little bit bigger so we seize on that so what's happening here as you can see is giving us exactly the same amount on all rows and what basically is happening is that power bi does not know how these two tables relate together so it just gives you the total amount on every row this is as simple as that so to create a relationship you have a few options if you are new you can actually go here to the Home tab and manage relationships and use it all to detect and the reason I recommend this is that most of the times power bi gets it right by itself so you don't have to worry about it since you found a new relationship it defines it by itself we close and we get the right results right away if we go back to our relationships view you can see that it created a relationship another way to do it is you remove that and then you say okay I know that Product ID and Product ID is the road that is in both tables and I know that these have unique Product ID values otherwise you won't be able to to create the relationship so you can just drag and drop and again power bi will create the relationship for you most of the times power bi will actually pick the right column right you would double click on it we could actually change some of the parameters we're going to go to the parameters later don't worry but here we can see that it just chose something for us so let's look at these options down here if we start with the cardinality drop down box and you have one to one one to many and many to one so if we go to our tables this is our product table and we have unique values for the product ID we can see a better on product name but the manufacturing table is giving us the number of products manufacturing a special day so of course the product products will repeat on this table so in this case as power bi correctly identified we have a many to one relationship okay one-to-many is in the other way and one-to-one is when we have unique values here and unique values there so this is a easy part really and power bi will do an excellent job picking up with cardinality to use most of the times the only problem could be that it shows the road the wrong column which normally doesn't do it actually I've never had that problem so now we move on to cross filtering direction we have both and single ok as you can see here you see the two arrows pointing on both directions you would have a click power bi so such that relationship to both the easiest way to explain this is if we set it first to single and see what happens so we're going to do that we're going to set the direction to single and of course it will go from one to the many and now let's do a simple expert when the direction has been set to single with between two tables who will work absolutely perfect so what we need to do is to bring another table here which I've already have here in any queries we're going to load the employee table let's do the at the neighbor load so here we have it so we have here our employees and here we have our employees ID we can look at the tables again so here we have employee ID and here we have our employee ID and what we want to do is we want to connect these two you have again a few choices to do it either you use we can test the outer detect no new relationships okay let's do it manually so you will set it as both but we want to have it a single just for the purpose of this demonstration to understand how these filtering flows through the tables so what we're going to do now we're going to create a table that has material from the product table it has the product name from the manufacturing table and the employee name from the employee table okay so let's do that we get from the manufacturing table the product name we get from the product table material and then from the employee employee name so we want to basically know which products have manufactured by who what materials they are allowed to work with so as long as you are not filtering anything on any of the tables the relationship will work and that is a tricky part because that's what sometimes streaks you to believe that you have a correct relationship set up but what happens if we would like to have if we would like to know for example which as how many employees can work with a specific material so what we want to do is we want to go from this table to disable and filter okay so this aside we want to have a simple measure count employees count employee name and now we are going to get material from the product and we're going to get count employees from the employee table and look what happens it happens exactly the same that happens in the beginning when we didn't have any relationship setup but we do have it that could be a little bit confusing and if you are importing a model from PowerPivot in Excel 2013 or earlier all the relationships will be set to single and the reason why I believe is because they didn't have the multi Direction feature yet so you have to be aware of that if you are importing models from Excel 2013 or earlier that you might have to go back to the relationships view and change the filters so what's happening what we are asking is to with materials get how many employees and the filter is flowing and then it you know the arrows indicates how the data if you would say flows through the tables so you can go through here but then there is no arrow on this table so you cannot actually cross basically it's like if it was there is no relation and that would be the other way around so the employee filters will flow to the manufacturing table but it cannot flow to the product table so if we change these to both suddenly the data can flow back and forth and let's see what happens we get the correct filtering okay so that would be the difference between single and both filtering directions so now you might be wondering okay but the nice easy I can just set all the relationships to both and then everything will work well not quite and if you demonstrating that I need to load a few more tables so let's do that first we're going to load this one the region table and the geographical table so region and here we have the other one and weight we're going to load also I don't know why I didn't do it the stock table so make sure that everything loaded correctly here we have them so now let's see how these relate to each other we have our lookup table for products and we have a stock table these tell us which products are in stock and we are going to connect these two together now this stock table has locations and we have that stock location on another table that connects with a continent I will show you the data and we have a table that connects continent with country and we have employees with countries so we want to get that - now look what happens here we're going to talk about it in just a second but let me show you the table so here we have the employee table with country ID country ID looks like this is a country and then we have the continent and then we have that a table with continent and stock location and our stock table looks like this with product names and product IDs that connect to our product table this is a little bit complicated but you're going to be able to download the file so don't worry you would be able to look at this in in more detail so what happened here as you can see this relationship was set as inactive why did power bi do that okay so let's say that we want to know which products are I don't know deliver from which countries so we want to pick up something from here to here and now we have set both cross filtering so you can say okay I should be able to get an answer right well you really cannot because power bi does not know which way you go should we go this way or should go that way and if you don't do the relationships automatically what we will do is you will set these as inactive so that is a way for power bi to resolve the conflict but that might not be the correct way perhaps this is the one that should be inactive so if you have that kind of table relationships you will have to set one of them at least as inactive another thing you can do to avoid this problem so every time you have like a circular relationship it won't work you could actually go back to edit queries you can duplicate the table back to your relationships and disconnect these and then you connect country ID with country ID as you can see once I have the relationship as they call now it's not it's not looking like a star but you know to do this the star diagrams they don't have circular relationships they are more like let me show you so this is the relationship at the forum that you you have to use in order to be able to have cross filtering set us both okay so there is no question about where power bi should how the data should flow the field drinks will flow from table to table so if you have a circular relationship like I show you then you won't be able to to set the relationship that's both so here you have it also okay so you have a fact table and then you have look-up tables all over so one last thing before we wrap up there is in options hmm data load here you have some settings for relationships here you allow power bi if you are important data from source that has relationships you power bi will copy those relationships for you so you don't have to create them or power bi doesn't need to guess what the relationships are and you can have this also check update relationships when refreshing queries if you know that your data model is going to change and for example one-to-one relationships will become one too many then you can have actually power bi check them out and update them for you I had some issues with that before so that's why I it I uncheck the type I will check it back to see how it does now and then you can have these the ability to alta to take new relationships after you loaded the data okay so this is all for today on Wednesday we're going to talk about many to many relationships where you don't have unique values on any other table so make sure you don't miss it and well this is all for today I hope you liked the video in that case please let me know I am always grateful for your feedback if you have any questions comments or suggestions let me know in the comment box or any of the social channels listed below and subscribe I publish power bi videos weekly on Doc's relationships not mine again yeah have a great evening bye

Channel: Curbal

Views: 165,193

Rating: 4.835391 out of 5

Keywords: Curbal, bi, cardinality relationships power bi, power bi designer, power, power bi video tutorial, powerbi, excel, Power bi, curbal, relationships powerbi, cross filter both relationships power bi, relationship power bi curbal, Curbal.com, power bi tutorial for beginners, power bi tutorial relationships, create realtionships in power bi, excel bi, power bi desktop, cubal, power bi relationships curbal, many to many relationship, relationships power bi, many to many power bi

Id: -4ybWQSRcOY

Length: 18min 48sec (1128 seconds)

Published: Mon Oct 17 2016