Relationships between tables are the key to successful data models in Power BI. There may be a lot of tables you are using, and if you have not yet defined relationships, then you need to check to see if relationships exist.
If you only concern yourself with a single table, then you always have a relationship between columns and a single row. That is, whatever is in the first column of row one is automatically related to the data in the second column of row one, and both of those are related to the data in the third column of row one; otherwise, the data makes no sense. When you build a visualisation using columns from the same table, you can count on the right data from each row automatically lining up. But if a relationship exists between two tables, then we get similar behaviour.
The relationship requires that each table has a column that contains values in common with values found in the other table. Ideally, in at least one of these tables, the values in the column must be unique. That is, each row in a particular column has a value that's not found in any other row of the same table and the same column. Power BI acts as if the tables are merged into one, but the results depend upon the type of relationship.
- A one‑to‑one relationship exists when each table has only one row containing the value they match on (top right example in the graphic above)
- Sometimes we have two tables with a one‑to‑many relationship (bottom right example in the graphic above). In that case, one table has unique values in a column that match up to one or more rows in the second table. And when that happens, the values in the table with unique rows get repeated with each of the related values in the second table, the one on the many side of the relationship.
- There is also a many-to-one relationship which is the opposite of one-to-many. For example, one table could have a list of actors, actresses and producers for one specific movie, which has a relationship with a table with one row for each actor, actress and producer in the database.
- Finally, there is a many-to-many relationship. This is where one table has many rows for one data field that relates to a table with the same format. For example, multiple records for one employee in the 'Employees' table who is a part of many projects, and a 'Projects' table rows for each project and employee ID.
When a relationship between two tables does not exist, there are clues. For example, when you try to create a visualisation by using one data field from one table with a data field from another, if a relationship has not been found, then the total sum of the values of the column being compared will be returned.
In the example above, the 'Movie Name' and 'Movies Count' columns come from a 'Movies' table, and the 'Runtime' column comes from a 'Movie Values' table. As there is no relationship between the two tables, the total sum of 'Runtime' is returned as opposed to the total run time for each movie.
It's very important after you set up a new data model and complete your work in the Power Query editor to next test the relationships between tables and look for this sort of behaviour to determine if relationships are missing or defined incorrectly.
