Relationships (one-to-many, many-to-many) in Power BI - Step-by-Step Guide

Introduction

Relationships connect tables in Power BI so you can analyze data together. They solve the problem of combining related data from different tables without merging them into one.

When you have a sales table and a customers table and want to see sales by customer details.

When you want to link product information to sales records to analyze product performance.

When you have multiple tables with shared keys and want to create combined reports.

When you need to filter one table based on selections in another table.

When you want to handle complex data models with many tables connected logically.

Steps

Step 1: Click

- Model view icon on the left side pane

The screen shows all tables as boxes with their columns listed

Step 2: Drag

- Primary key column from one table

A line appears following the cursor

Step 3: Drop

- Foreign key column in the related table

A relationship line connects the two tables

Step 4: Click

- Relationship line

The Edit Relationship pane opens showing details of the relationship

Step 5: Select

- Cardinality dropdown in the Edit Relationship pane

You can choose One-to-many or Many-to-many relationship type

Step 6: Click

- Close button in Edit Relationship pane

The relationship is saved and active for use in reports

Before vs After

Before

Tables are separate with no lines connecting them; reports cannot combine data from both tables.

After

Tables show a line connecting related columns; reports can combine and filter data across these tables.

Settings Reference

Cardinality

📍 Edit Relationship pane

Defines how rows in one table relate to rows in another

Default: One-to-many

Cross filter direction

📍 Edit Relationship pane

Controls how filters flow between the two tables

Default: Single

Make this relationship active

📍 Edit Relationship pane

Determines if the relationship is used by default in calculations

Default: Checked

Common Mistakes

Creating a relationship on columns with duplicate values on both sides

This causes ambiguity and incorrect filtering because Power BI expects one side to have unique values in one-to-many relationships

Ensure one table column has unique values (primary key) before creating a one-to-many relationship

Setting cross filter direction to Both without understanding data flow

It can cause unexpected filter behavior and performance issues

Use Single direction unless you need filters to flow both ways and understand the impact

Summary

Relationships link tables so you can analyze combined data without merging tables.

One-to-many is the most common type where one table has unique keys and the other has duplicates.

Many-to-many relationships allow linking tables with duplicates on both sides but require careful use.