Power BI Data Modelling Explained: Primary Keys, Relationships, Cardinalities & Best Practices

Posted by

September 21, 2026

On February 9, 2026

Introduction

In this blog, you’ll learn how primary keys and foreign keys work together to create relationships between tables. The impact cardinality and cross-filter direction make on your reports, and the best practices for building a clean, efficient data model. Whether you’re a beginner or looking to strengthen your Power BI skills, this guide will help you design models that deliver meaningful insights.

Understanding Data Modelling

Data modelling is the process of creating relationships between multiple datasets. These relationships are established by relating to the primary key and foreign key. Once relationships are in place, these datasets act as a single data model that can be used for the purpose of visualization and reporting.

Primary Keys (or a Unique Identifier):

The primary key is the field that uniquely identifies each row in the table, or in other words, the primary key cannot have duplicates and will act as a unique identifier. In our case, the product code in the product table will act as a primary key.

Foreign Keys:

A foreign key is the field that refers to the primary key in another table. Foreign keys can have duplicates. In our case, the Product Code in the Sales Table will act as a foreign key for the Product Code in the Product Table.

Deleting Relationships

Relationships between datasets are automatically detected by Power BI based on columns/field names and values. However, sometimes this guesswork by Power BI can be incorrect. Let us discuss how you can delete and re-establish the relationship to rectify the error.

To delete a relationship:

Switch over to the Relationships tab from the left pane by clicking on the Relationships icon shown in the screenshot.

Right-click on the connection line between the two tables and select “Delete” or press the delete key on the keyboard.

Hiding Tables and Fields

To keep the data model clean and light, we need to hide some of the unrequired tables from the report view. By doing this you make your life easy, as you don’t see unnecessary items. To hide tables:

On the Relationships tab on the left pane.
Right-click on the Country Table.
Click Hide in report view.

Creating New Relationships: Drag and Drop

There are two ways of creating a relationship between two tables. The first one is by a simple drag-and-drop method, and the other is via the Manage Relationship button in the Home tab in the ribbon. We will discuss the drag-and-drop method first. Use this method if you already know the primary and foreign keys beforehand.

To create relationships using this method:

Click on the Product Code field in the Product Table and keep the mouse button clicked.
Drag the mouse to the Product Code field in the Sales Table, still holding the mouse button.
Release the mouse click while on the Product Code field in the Sales Table.

Detailed discussion available at Best Data Modeling Practices | Power BI for Beginners

Editing Relationships Using Manage Relationships

While in the Relationships tab, click the Manage Relationship button in the Home tab of the ribbon.

Click on the ellipsis (⋯) of the existing relationship and click edit and change its cardinality.

Click Save.

The Manage Relationships pop-up appears.

Click Close.

Cardinality and Cross Filter Direction

There are two important concepts in establishing the relationship between two datasets. These are Cardinality and Cross Filter Direction. Let’s discuss them in detail.

Cardinality:

Cardinality is the type of connection established between two tables. There are four types of cardinalities in Power BI. The relation between tables is denoted by two symbols, an asterisk (*) and 1, where the asterisk (*) represents many sides and 1 denotes a single side.

Many to One (*:1): This means the foreign key column in each table can have more than one instance of a value, and the other related table, having the primary key, has only one instance of a value.
One to One (1:1): This means one primary key from the dimension table will be linked to just one foreign key in the fact table.
One to Many (1:*): This means the primary key column in one table has only one instance of a particular value, and the other related table having the foreign key can have more than one instance of a value.
Many to Many (*:*): A many-to-many relationship between tables removes the requirements for unique values in tables. This setting indicates that there are multiple records for the single value in both the tables.

Cross filter direction:

The arrow in the centre of the line represents the direction of filtering being applied to two tables and is called Cross Filter Direction. There are two types:

Single: This is the default type. It means that filtering flows from the dimension table towards the fact table.
Both: This means filtering flows from dimension tables towards the fact table and vice versa. Both is used in rather complex data models and should be used with caution, as may lead to unexpected results.

Conclusion

Data modelling is the foundation of creating accurate and meaningful Power BI reports. By understanding how primary keys and foreign keys work and by building proper relationships between fact and dimension tables, you ensure your visuals reflect the right insights. Power BI provides flexible tools like drag-and-drop, Manage Relationships, and visibility controls to help you refine your data model. Mastering concepts like cardinality and cross-filter direction further improves the reliability of your dashboards. With a clean and well-structured model, Power BI becomes a powerful tool for transforming data into actionable decisions.

FAQs

What is data modelling in Power BI?

Data modelling is the process of connecting multiple datasets using relationships, so they work together as one unified model for reporting and visualization.

What is a Primary Key?

A primary key is a unique identifier in a table. It usually appears in dimension tables and cannot contain duplicates.

What is a Foreign Key?

A foreign key is a field in a table that refers to the primary key in another table. It can contain duplicate values and normally exists in fact tables.

Further Learning and Resources

To learn more about Power BI, check out our courses here:

Want to learn how Microsoft 365 Copilot transforms everyday work with AI? Check out our Course:

Microsoft 365 Copilot Training Courses

Want to learn more about Power BI and how to make reports more efficiently?

Power BI training in Australia: We offer comprehensive training sessions across various cities, including :

Power BI Crash Course eBook: Download our detailed guide to take your Power BI expertise to the next level, covering topics from basics to advanced with practical examples and step-by-step instructions. (Limited time offer)

Download the Beginner’s Guide to Power BI

Ready to enhance your Power BI skills? Sign up for our training courses and explore our wide range of resources designed to elevate your data analytics capabilities.