All language subtitles for 6. Creating Table Relationships in Power BI Desktop

It guys it's an exciting moment in the Adventure Works world because we are about to wire up our data

model and create our table relationships.

So in power be a desktop.

There are two common ways to do this.

Option number one which I prefer is to simply click and drag to connect your keys in the relationship

view.

So it's as simple as clicking on a foreign key dragging the mouse finding its primary key match and

releasing to create that relationship.

Very very simple very straightforward.

Also a very visual way to create those relationships.

The second option is to add or detect relationships using this manage relationships dialog box.

So Same idea here.

Just a little bit more manual.

You would manually select the two tables that you'd like to relate as well as the primary and foreign

key columns.

So without further ado let's head into power be-I and add these relationships.

All right.

So here I am in the relationships view and I'm going to show you the slow way to create a relationship

first and then we'll never do it that way again.

And we use the manage relationships button in the home to.

And as you can see I have a couple of options here you can choose auto detect and basically say I don't

know what my relationships are going to look like.

Why don't you analyze the tables and tell me whenever I've played with it.

It usually gets some of the relationships right but Mrs. others so let's go ahead and disregard the

auto detect option and we're going to add a new relationship manually and we're going to connect our

sales table to our customer table.

So A.W. sales to A.W. customer and without even clicking on a call on power be I automatically identified

the foreign and primary key customer key column that matches these two tables together and it gives

us a little bit of information about the cardinality and the cross filter.

We're going to talk about both of those in-depth as well as this make relationship active option later

on this section.

But for now that's good enough.

Press OK.

And close and there you go.

We've created our first table relationship between sales and customer look up and you'll see as you

hover over this relationship line it kind of highlights what the key column is.

You see that box around the customer key.

So that's option number one the manual way.

The easier way is to click and drag.

So to connect calendar to SALES I'm just going to grab my order date and we'll talk about whether or

not to use order versus stock in a little bit.

But for now let's focus on order date and we're going to click and hold the mouse and just drag up and

release it over the date field which is our primary key in the calendar look up and there you go we've

created our second relationship.

Same story with territories.

Let's grab that territory foreign key drag it out to the sales territory key in the look out and now

before we connect up the product look out I want to return back to my report view for just a second

and remind you that we had been looking at order quantities and trying to break them down by product

names to no avail because remember without the relationship these two tables are essentially islands

that can't talk to each other.

But now when we go back to relationships grab that product key drag it up to our product look at and

it's going to be tricky.

There is Product Key created the relationship and now drumroll please go back to report and look at

that we've got correct values for quantities broken down by product name.

And again this could be any field from product table all by simply dragging our mouse making a couple

of clicks and creating that relationship between the two tables.

So it's up back into our relationships view.

And I'm just going to drag things around a little bit.

You don't have to organize things exactly like I am really doesn't make any difference.

Now I'm going to hold off on these two tables.

We're going to talk about these in the next lecture and actually or rearrange kind of in a hierarchical

way product subcategory category.

But for now we're good to go.

So congratulations.

We've officially just built an Adventure Works data model.