The establishment of relationships between tables is an essential and fundamental activity in the data modeling of Power BI. Creating relationships between tables in Power BI is crucial for building effective data models, which allows to use different sources of data efficiently.
Relationships between tables define how data connects, with cardinality showing the number of related records. Primary/foreign keys link tables, and cross-filter direction controls filter flow.
Relationships Between Tables
While each table represents one dataset, two or more tables may relate to each other. This relationship between tables allow to create reports and visualizations based on data from multiple tables. For example, an employee belongs to a certain department and an order is placed for a certain product managed by that department. This shows a relationship between an employee, work department, products and orders managed by that department.
In Power BI, there are some principles and properties which we can use to define a relationship between tables.
- Primary key and Foreign keys
- Cardinality (Relationship between Tables) – One-to-One, One-to-Many, Many-to-Many
- Relationship Direction
In this article, we will learn about these concepts to define a relationship between tables.
Primary key and Foreign key
In Power BI, there isn’t a direct concept of “primary key” and “foreign key” like in relational databases (SQL, PostgreSQL, SQL Server), but these principles still apply to relate tables (datasets) with each other. Power BI uses relationships between columns in different tables (datasets) to simulate the behavior of primary and foreign keys. To understand the relationship between tables first we should know about the Primary and foreign key relationships.
Tables are related to other tables by either a primary key or foreign key relationship. consider Primary and foreign key relationships as sort of a “parent/child” relationship in programming.
Primary key
Primary key is a unique identifier of records (rows) in tables. It is column or a set of columns (combined) that uniquely identify a row in the table. It ensures that no two records (rows) can have the same value for this primary column field. This is very crucial for the data integrity. In Power BI any column or combination of columns, would be considered a primary key if don’t have duplicates or Null values for any record (row).
A relational database (like SQL, PostgreSQL, SQL server) by design, enforces primary key uniqueness by allowing only one record (row) of the given value for primary key column in the table or dataset.
Foreign key
Foreign key is a column or a set of columns in one table that refers to the primary key in another table. This is used to create relationships between two tables. The presence of the foreign key on a table column creates a relationship with the primary key on a column in another table. This creates a logical relationship between tables (datasets). In Power BI, the foreign key is the column that connects to a related column (primary key column) in another table.
In that regard, consider Primary and foreign key relationships as sort of a “parent/child” relationship in programming. We can have a database designate some column in the “child” table as a foreign key that refers to some column in the “parent” table as primary key.
This relationship enforces the primary-foreign key rule between tables. Rows will be added or updated in the child table if the value in foreign key columns matches with an existing value in referenced primary key column of the parent table.
Primary key and Foreign key in Power BI
Primary Key in Power BI
- In fact table, primary key is generally a column that uniquely identifies each record in that table. The fact table usually contains transactional data like sales, orders, etc.
- In a dimension table (like a product or customer table), the primary key is often a column that identifies individual entities (like ProductID, CustomerID).
- Power BI uses a unique column in one of these tables to establish relationships.
Foreign Key in Power BI
- Foreign Key Relationship – Foreign key in one table (usually the fact table) points to a primary key in another table (usually a dimension table). This is how we create a relationship between tables in Power BI.
Primary key and Foreign key Example
| SalesID | ProductID | Amount |
|---|---|---|
| 1 | 1001 | 800 |
| 2 | 1002 | 650 |
| 3 | 1001 | 810 |
| ProductID | ProductName |
|---|---|
| 1001 | Laptop |
| 1002 | Mobile Phone |
- Sales Table (Fact Table) contains a column
ProductID(the foreign key). - Product Table (Dimension Table) contains a column
ProductID(the primary key). - With
ProductIDcolumn, we have have created a relationship between these two tables. This is represented as primary key-foreign key relationship.
Primary key and foreign key relationships are employed in relational databases to identify One-to-one, one-to-many & many-to-many relationships between tables.
Troubleshooting Issues with Primary key and Foreign key
- Duplicate Keys – If the “primary key” column in the dimension table (e.g., ProductID in the Product table) contains duplicate values, Power BI will not be able to create a one-to-many relationship.
- Missing Data – If the foreign key column in the fact table (e.g., ProductID in the Sales table) contains values that don’t exist in the dimension table, we might encounter missing or unexpected results in our reports.
Best Practices with Primary key and Foreign key
- Ensure that the primary key column has unique values.
- Avoid null or blank values in the foreign key column as it might lead to unmatched rows in the primary key table.
- Use appropriate data types for both primary and foreign key columns.
- Power BI doesn’t explicitly name columns as “primary” or “foreign” keys, understanding the concept of relationships between tables will help to structure data effectively using these key principles.
Cardinality : Relationship between Tables
In relational databases, cardinality defines as the relationship between tables, specifically the number of instances of one table (entity) that can be associated with instances of another table (entity). Cardinality is a key concept to design database schema, as it defines how tables relate to each other. In Power BI, there are three types of relationships that can be established between tables:
- One-to-One
- One-to-Many
- Many-to-Many
One-to-One relationship
It is a relationship between two table such that a row in one table (entity) has exactly one related row in the other table (entity) and vice-versa. In other words, we can also define One-to-One relationship as – each record in Table A corresponds to exactly one record in Table B, and vice versa. This relationship can be created using Primary key – Unique Foreign key constraints.
One-to-One relationship example
We have two tables.
UsersTable (Primary key – Customer Id)OrdersTable (Primary key – Order Id, Foreign key – Customer Id)
In the Orders Table, we have only one record for each customer. From the diagram, we have Unique (One-to-One) Customer Id mapping. It show One-to-One relationship between Users table and Orders table.
One-to-Many relationship
One-to-many relationship between two tables is defined as one row from one table can have multiple matching rows in another table but not vise-versa. One-to-Many relationship can be established through the use of a Primary key-Foreign key relationship.
In other words we can define One-to-Many relationship as – a single record in Table A can be related to one or more records in Table B, but each record in Table B is related to only one record in Table A.
One-to-Many relationship example
We have two tables.
UsersTable (Primary key – Customer Id)OrdersTable (Primary key – Order Id, Foreign key – Customer Id)
In the Orders Table, we have multiple orders (records) for each customer. From the diagram, we have One-to-Many relationship between Users table and Orders table.
Many-to-Many relationship
Many-to-Many relationship is defined as One row of first table have many matching rows in another table, and a row in another table can also have many matching rows in the first table. In other words, we can define Many-to-Many relationship as – a record in Table A can be related to multiple records in Table B, and vice versa
Many-to-Many relationship can be developed using a third table commonly referred to as a “Junction table” or “Bridging table”. A junction or bridging table can act as a connecting point for the two tables through Many-to-Many relationship. Number of attributes of the relationships between two tables are assumed to be stored in this Junction or Bridging table.
Many-to-Many relationship example
We have three tables.
StudentsTable (Primary key – Student Id)SubjectTable (Primary key – Book Id, Foreign key – Class Id)- Multiple students can be in the same class and a class can have multiple students. This is represented by junction table.
Student_ClassTable (Junction Table) – This table represents the relationship between student and class.
Cardinality in Database Design
- Reference – Cardinality helps enforce data integrity by ensuring that relationships between tables remain correct.
- Foreign Keys – One-to-many and many-to-many relationships typically require the use of foreign keys to link tables together.
- Performance – Understanding the cardinality of relationships helps optimize queries and improve database performance.
Relationship Direction (Cross-filter Direction)
Relationship direction is also referred as Cross-filter direction. This concept is most used in Data Modelling tools like Power BI and some time in relations databases as well. Relationship direction defines the direction in which the flow of filtering is crossed in the relationship between tables. In other words, it defines how filters applied on one table can impact the data in related tables.
In Power BI, understanding of Cross-filter direction helps to determine how data is propagated across related tables in queries, reports, or dashboards. There are two kinds of relationships in Power BI.
- Unidirectional (Single direction)
- bidirectional (Both directions)
Unidirectional Relationship
Unidirectional relationship defines where Filters applied on one table flow in only one direction, affecting data in the related table but filtering in the related table does not affect the first table. This means filters can flow in only one way.
For unidirectional in the case of One-to-Many relationship between parent table and child table – filters from a parent table will impact the child table but not the other way around.
For example – For the Customers table and an Orders table, applying a filter on Customers table can filter the relevant orders in the Orders table, but filters applied on Orders table won’t affect the Customers table.
Unidirectional relationship are simpler and more efficient to process. It avoids ambiguity in many-to-many relationships. But this is not very helpful in complex queries and reporting.
Use Case – Unidirectional relationship is the default setting in most relationships since it maintains simpler and more predictable query behavior.
Bidirectional Relationship
Bidirectional relationship defines where Filters can flow in both directions. It means filter applied on either of the related tables will affect the other as well.
Filters applied on one table will affect data in the related table and filtering in the related table will also affect the first table. This means filters can flow in both ways.
For example – For the Customers table and Orders table, applying a filter on Orders table will filter related customers in Customers table, and applying a filter on Customers table will filter the relevant orders in Orders tables.
Bidirectional relationship are more flexible for complex queries and reporting. It helps in filtering multiple related tables dynamically. But this can cause circular dependencies and performance issue for large dataset.
Use Case – Bidirectional filtering is useful when we need dynamic filtering from both tables. This is mostly useful in complex reporting scenarios where tables are deeply interconnected, or when we have multiple fact tables sharing dimension tables.
Cardinality & Cross-filter Direction
Generally, cardinality and Cross-filter direction are related to each other.
| Cardinality | Cross-filter Direction |
|---|---|
| One-to-One | Unidirectional (Single Direction) |
| One-to-Many | Unidirectional or Bidirectional |
| Many-to-Many | Unidirectional or Bidirectional |
Create Relationships in Power BI
We need to follow the following steps to create the relationships between tables in Power BI.
- Load Data – First we have to load Data.
Import data into Power BI from various sources.
- Open Model View
Click the “Model” icon on the left navigation pane to expose the tables.
- Create Relationship
- Simply drag a table’s primary key onto another table’s associated foreign key.
- In the properties pane that opens up, fill in the settings for the relationship-selected type of relationship and/or cross-filter direction.
Note
- Power BI has a “Manage Relationships” feature whereby we can view all the existing relationships and manage them accordingly.
- We can click on manage relationship and edit it as per requirement or it can be deleted at any time.
Summary
In this article, we learned about Relationships between tables. Following topics were discussed: