While data tables make up the bulk of a data model, relationships make the frame. Relationships are the bones, and tendons, and ligaments that create structure, without them, we would have a flat gelatinous blob of data. Power BI has a relationship auto-detect feature that's pretty good. But it doesn't always get it right so it's important that you know what a proper relationship works like and how to repair problematic relationships. Tables alone can't talk to each other. Let's take these two tables.

Our fact table lists all the transactions, but by looking at these transactions, we can't really tell what happened. We can see some amounts, but amounts of what? I can't tell because the only identifier are these IDs and they don't really mean anything to me, the human, but I do think that I've seen these ideas somewhere before, on our products table maybe? Oh, there it is. The Fabrikam laptop, white which matches product key 345. So using my human logic, I can compare these two matching IDs and confidently determine that this was a sale of Fabrikam laptop, white. That's what relationships do, they connect items from table A to their matching items in table B. If we have no relationships between these two tables but we attempt to create a visual using data from both tables, we are going to get this weird result where the numbers all look maxed out.

What's happening here? Well, we're seeing all the product labels listed and we're seeing all the sales listed but these sales numbers aren't being categorized by product, the model is simply listing all in each table. If I go into model view and create this relationship, I am teaching the model that when you see product ID 345 on the fact table, connect those IDs to the matching product IDs on the products table.

Now our model can identify the sales that involved Fabrikam laptop, white, and summarize that information for me. And not only can it do this for Fabrikam laptop, white, it can do it for all the other products as well, and show me the results in a nice tidy visual. This is why relationships are critical in data modeling. It is very rare for a table to be completely disconnected from the rest of the table. But not all relationships are the same, let's learn about the special behaviors of different types of relationships.

While your modeling career will undoubtedly involve plenty of properly functioning relationships, you will also inevitably encounter problematic ones and you'll need to know how to resolve them. Let's take a look at the relationships in this model by selecting manage relationships from the home ribbon in model view. Each line represents a relationship in our model, let's open one to take a look. So this is an active relationship between the fact sales table and the channel table, and it uses the columns called channel key in each table.

Below are two drop-down menus and they represent very important topics. Relationship cardinality describes the parent-child-like relationship between the values in each column. And there are four different types, many to one, one to one, one to many, and many to many. Cardinality describes how items in the first column behave as a set and also how items in the first column match with items in the second column. You very well may encounter every one of these cardinality types, so let's get to know them.

The many to one and one to many cardinalities describe twins scenarios. If we have two tables being related in one of these tables we are going to see the channel each listed only once. In the other table, we are going to see the channel listed many times. This describes a one-to-many relationship. The only difference between many to one, and one to many cardinality types is which tables selected first in this Power BI dialog box. If I swap the tables around, notice it just flips those terms, makes perfect sense. Many to one and one to many cardinalities are the most common type of cardinality but the other ones are there for a reason and we will run into all of them eventually.

One to one is a cardinality for which each record in table A is associated with one and only one matching record in table B. For example, this company has one store manager for every store. When the store manager table is related to the stores table, since each store has only one manager, and each manager only manages one store, this will create a one-to-one relationship. I can visually identify this by looking at the end points of this relationship where I can see a number one on each end. This relationship is simple and pretty user-friendly.

The many-to-many relationship type describes a scenario in which table A has parent values for which there are multiple children in table B. And those same children can act as parents in table B, having multiple children in table A. A common example of this is orders and products. One order might list many different kinds of products, and each product has probably been purchased in many different orders.

Many to many relationships were not allowed in Power BI until 2018. And the workaround for these types of relationships was to create a bridge table that contained a list of unique values from both tables. And then relate this unique table to both of the many tables. Then in 2018, Power BI introduced the many to many relationship type, which made it a bit easier to handle these relationships in Power BI. But as we're about to see, there are still lots of limitations.