Joining tables in the Data Model.

A data model is a structure which joins several tables in a report together, they are joined by creating a relationship. If we want to create a visual which uses fields from more than one table, we must have a relationship between those two tables the most common type of relationship is a one to many.

We use the Data Model View in Power BI to create those relationships, it uses a drag and drop feature which is easy to use. However, it will only work correctly if the underlying data in the tables is correct, so for example a common field must exist in both tables and for a one to many relationship the values must be unique in one of the tables, but in the other table those values can be repeated many times. Let's do a workout and see how to create a one to many relationship in Power BI.

So I'm just going to go to get data and Excel workbook and I'm taking module 6 lesson three workout.

Now I've got two tables in this workbook, so I'm just going to select both of them here transactions and customers and load them into my report.

First thing I'll do here once they're loaded is have a quick look in Data View at what the data actually is.

If I select the transactions table first, we can see that I have a list of transactions the date of the transaction, the amount and the only information I have about the customer who made that transaction is an ID number.

Now, we could have the same customer making several transactions, so the customer ID could be repeated many times in this particular table.

If I click on the next table customers, we will see here we have more information about each of our customers. So what their job is and what state for example they live in.

If I look at the customer ID field here, so it's the same field again that was in the transactions table but in here I would have each ID only one time, so each customer has their own unique ID.

Now, I'm just going to go back to Report View and try and create a visual, what I would like to do is create a bar chart, so if I just click here on bar that uses the amount field from transactions and I would like to see what the total amount is per state. Iif I click on the State field here and just open the chart out we can see it looks a little odd because right away each of the states has exactly the same amount. Now, what are the chances of that? It's got the total amount, it hasn't been able to filter it out per state because the State field is in a different table and there's no relationship.

If I go down to my data Model View here, just click here, I can see the two tables that I'm using and I need to create that relationship between the two on a one-to-many basis. So in the customers table, the joining field customer ID will be unique but in the transactions table, it can be repeated many times. So I'm just going to drag from customers table to the transactions table.

And we can see here the data allows us to do that and we have a one-to-many relationship.

No, if I click back on my Report View, my bar chart has already been corrected.