All language subtitles for 11. Connecting Multiple Data Tables in Power BI Desktop

It's time to shake things up a little bit to this point we've been dealing with a pretty simple straightforward

model just our one data table sales data table and a bunch of individual lookups that we've created

connections or relationships to.

Now here's the thing.

I just got off the phone with our client and Adventure Works and it turns out they have another data

table with information about product returns that they'd like us to integrate into the model as well.

So in other words they don't just need us to analyze product sales but they want to get a sense of which

products are being returned most often as well.

So what we're dealing with here is something like this.

In this particular case we've got a simplified model with three lookup tables.

Customer look up calendar and product look up and two data tables sales data and returns data each with

its own set of dates and other foreign keys.

But take a minute and look at the relationships here.

What you'll notice is that the sales data is connected with one to many relationships to all three of

the lookups.

Customer look up calendar and product look up.

But the thing is the return's data is only connected to the calendar and the product Look-Up.

We don't have any way to connect return's data to customer look up since we don't have a foreign customer

key and this isn't entirely uncommon especially for larger or more complex real world models where you're

pulling in data from a number of different sources.

It's actually quite uncommon to have the exact same set of foreign keys in every source data table that

you're working with.

So this is the type of scenario that you may very well run into in a real world application.

So what this means is that we can analyze sales and returns data sales quantities and returns quantities

within the same view or visual and power be-I but only if we filter or segment our data using fields

from shared lookup tables.

So looking at the returns data in this particular case we know which product was returned because we

have a product key and we know which date the product was returned because we have a return date.

But we know nothing about the customer who made the return.

So there's no way we could slice or dice or filter that returns data by any field in the customer looked

like names marital status gender income or any of those fields.

So what you may be noticing here is that we've successfully integrated that returns data into the model.

But we did not attempt to make any kind of relationship between the returns data and the sales data.

And the reason for that is that no matter which fields we tried to use to form a relationship between

those two data tables we're always going to run into a many to many relationship error.

And that's because both data tables sales and returns have multiple dates and the date fields.

They have multiple products and the Product Key field multiple territories in the Territory field and

so on so forth.

So even if we wanted to connect those two together there would be no way because we have no field that

would give us a valid one to many relationship.

So that's a very very important point.

Generally speaking you will never create direct relationships between data tables.

Instead you connect them indirectly through shared lookup tables.

So what we're going to do now is jump back into power be-I we actually have that returns the file that

we're going to load up and then we're going to wire into our model creating relationships just like

the ones you see here.

All right so once you're back in the relationships view beard venture works report go ahead to the Home

tab and click get data.

We're going to get our last filed the projects.

It's another CXXVI and this time we want adventure works.

Underscore returns.

So go ahead and double click.

Take a look at the preview.

Pretty simple dataset here.

Got a return date.

The territory key product key and that numerical quantity field as well.

So let's edit this file to fire up the query editor and first things first said to our table name call

this one.

A.W. returns.

Check our headers.

They're promoted.

We've got dates for a return date.

Whole numbers for territory key product key and quantity.

Very straightforward.

All looks good.

Let's go ahead and just close and apply just like it is and we'll see that object pop in right here

in our relationship view

where we go.

So here's our second data table.

Click and drag and make some room for it here.

And I want to make it clear that these two tables here are the two data tables that we care about.

Now remember I can't just grab a field a foreign key from my returns table and try to force a connection

right to my sales table because I'll get that many to many relationship error can't create a relationship

because one of them must have uniques press OK and the same holds even if I try to use my keys territory

kid territory he can't do that either.

So again the way that we integrate a second data table or a third or fourth or fifth for that matter

is by wiring it and creating the same relationships just like we would with any other table.

So territory key can connect to territory key return date can connect to calendar date and then last

but not least Product Key can connect to product key and by association product subcategory and category

as well.

So again no customer key means that we can't form a relationship with that customer lookup table.

Now to really hammer that point home and understand how this impacts our analysis let's jump all the

way back up to the report view.

And as you can see we've still got our matrix here showing product name and order quantity.

So what I'm going to do is select that Matrix could see my returns table is populated here I'm going

to grab a return quantity and pull it in right under order quantity in my values and it can expand to

view and take a look.

This looks great.

I'm now seeing order quantities and return quantities side by side in the same visual broken down by

product name.

So that's really helpful.

And I can get a lot of insight from this but what if we want to see the same view the same values broken

down by gender instead.

I'm curious if men or women tend to return items more frequently.

So instead of product name let's pull that right out of our matrix and let's go ahead and drill into

our customer look at and right here we can find gender pull that into rows and take a look at this.

We see the order quantity which seems to be correct but for return quantity get that same repeating

number issue that we've seen in the past and almost every time you see that repeating number issue it's

because of a relationship problem.

And in this case the relationship problem is that we're trying to apply a filter from the customer table

and show values from returns which is an unrelated disconnected table and that goes back to the point

that I made earlier.

You can only view fields from both of these data tables in the same visual when you're filtering or

segmenting using a field from a common or shared look up since the customer table is not shared by both

of these.

This is an invalid view and that's why we see these duplicate erroneous values.

So let's jump back to our relationships to you.

And this is our finalized data model.

So we've got all of our tables in here.

We've got all of our relationships in place.

This is going to set us up for some really interesting analyses down the line.

But before we move on to calculated fields with daks and then visualisations we need to hit one more

really important concept when it comes to data modeling which is the filter flow.

So stay tuned.

That's coming up next.