.svg){kind=small}
How to Use Relationships to Combine Multiple Tables in Power BI
Introduction
When you first start using Power BI, it’s common to load one table and build a few charts. But real reports usually need more than one table.
For example:
- Sales transactions in one table
- Customers in another
- Products in another
- A calendar table for dates
If you try to cram everything into one giant table, it can become messy and slow. Power BI works best when you keep tables separate and connect them with relationships.
A relationship is a link between two tables, based on a column they share (like CustomerID or ProductID). Relationships let you combine data without physically merging tables together. That means you can build visuals like:
- Total sales by customer region
- Sales by product category
- Sales over time (by year, month, quarter)
- Top customers and their purchase history
In this guide you will learn:
- What relationships are and why they matter
- How to set them up in Model view
- One-to-many vs many-to-many (and why it matters)
- Which direction filters should flow
- How to build a simple star schema (best practice)
- Common relationship problems and how to fix them
- When to use relationships vs Power Query merges
- How relationships affect DAX measures and visuals
Everything is explained in plain English with practical examples.
1) Relationships vs merges: what’s the difference?
Power BI gives you two main ways to “combine” data:
Relationships (Model view)
- Tables stay separate
- Power BI connects them on matching values
- Best for reporting and analysis
- Flexible and scalable
Merge queries (Power Query)
- Tables are physically joined into a new table
- Similar to VLOOKUP or SQL JOIN
- Best when you truly need one combined table for cleaning or shaping data
A simple rule:
- If you want to analyse data across tables in visuals, use relationships.
- If you need to create a new table during data prep, use Merge in Power Query.
Most Power BI reports use relationships as the main approach.
2) The key idea: fact tables and dimension tables
To understand relationships, it helps to understand table roles:
Fact table (transactions)
A fact table contains detailed rows, usually one row per event, like:
- one sale
- one invoice line
- one website visit
- one support ticket
Examples: Sales, Orders, Timesheets
Fact tables usually have:
- many rows
- numeric fields to sum (sales amount, quantity, hours)
- ID columns that link to other tables
Dimension table (descriptions)
A dimension table describes the things in the fact table:
- Customers (name, region, sector)
- Products (category, brand, size)
- Dates (year, month, weekday)
Dimensions usually have:
- fewer rows
- descriptive fields used for slicing and grouping
In most models, you connect:
- Fact table in the middle
- Dimension tables around it
This is called a star schema.
3) A simple example model
Imagine you import these tables:
Sales (fact table)
- SaleID
- Date
- CustomerID
- ProductID
- Quantity
- SalesAmount
Customers (dimension)
- CustomerID
- CustomerName
- Region
- Sector
Products (dimension)
- ProductID
- ProductName
- Category
- Brand
Calendar (dimension)
- Date
- Year
- Month
- MonthName
- Quarter
You want to show:
- Total Sales by Region
- Total Sales by Category
- Total Sales by Month
That’s exactly what relationships are for.
4) How to create relationships in Power BI
Step A: Go to Model view
In Power BI Desktop, select Model (the diagram icon on the left).
You’ll see your tables as boxes. If Power BI can detect relationships, it may draw lines automatically.
Step B: Create a relationship manually
If a relationship isn’t there, you can create it:
- Drag
CustomerIDfrom Customers ontoCustomerIDin Sales - Power BI opens a relationship box
- Confirm details and click OK
You can also go to:
- Home > Manage relationships > New
5) Understand relationship settings (the important ones)
When you create or edit a relationship, you’ll see key settings:
A) Cardinality
This describes the “shape” of the link:
- One-to-many (1:*): one customer has many sales
- This is the most common and best option.
- Many-to-one (*:1): same relationship, just viewed from the other side.
- One-to-one (1:1): rare, usually for splitting a table.
- Many-to-many (:): more complex and can cause confusion if not handled carefully.
B) Cross filter direction
This controls how filters travel across the relationship.
Common options:
- Single (recommended most of the time)
- Both (use carefully)
In a star schema, filters usually flow:
- from dimension tables → into the fact table
That’s usually Single direction.
C) Active vs inactive relationships
Only one relationship between two tables can be active at a time (in most cases).
If you have multiple date columns (Order Date and Ship Date), you might have:
- one active relationship to Calendar
- one inactive relationship you use with DAX when needed
6) One-to-many relationships (the “gold standard”)
This is the relationship type you should aim for most of the time.
Example:
- Customers[CustomerID] → Sales[CustomerID]
Meaning:
- Each customer appears once in Customers
- The Sales table may contain that CustomerID many times
Why this is great:
- It keeps your model clean
- It makes measures behave predictably
- It avoids double counting
How to check “one side” is unique
In the Customers table:
- CustomerID should have no duplicates and no blanks
If it does, relationships become less reliable.
7) Many-to-many relationships (when they happen and what to do)
Many-to-many happens when both tables contain duplicates in the linking column.
Example:
- A table of Sales and a table of Targets both have multiple rows per ProductID and Date
Power BI can create a many-to-many relationship, but it can cause:
- confusing totals
- unexpected filtering
- performance issues
Better approaches
- Create a proper dimension table (unique list of keys) and relate both tables to it
- Use a bridge table if needed (common with tags or categories)
If you can turn many-to-many into two one-to-many relationships, your model usually becomes much easier to manage.
8) Filter direction: why “Single” is usually best
In a star schema:
- Customers filters Sales
- Products filters Sales
- Calendar filters Sales
But Sales usually should not filter Customers in a strong way. If it does, you can get strange results when multiple fact tables exist.
So a standard setup is:
- Single direction from dimension → fact
When “Both” can be useful
Sometimes you need “Both” when:
- you have a bridge table
- you need a filter to flow through multiple tables
But “Both” can also create ambiguity (Power BI doesn’t know which path to use), especially when you have multiple ways to connect tables.
If you use Both, test carefully.
9) How relationships affect visuals (what’s really happening)
Let’s say you create a measure:
Total Sales = SUM(Sales[SalesAmount])
Now you build a chart:
- Axis: Customers[Region]
- Values: [Total Sales]
Even though Region is in the Customers table, Power BI can still sum SalesAmount correctly because:
- Region filters the Customers table
- The relationship passes that filter to Sales
- Sales rows match the filtered customers
- SUM adds up only those rows
That’s the power of relationships.
10) Build a proper Calendar table (so time analysis works)
Time is one of the most common reasons people struggle in Power BI. A Calendar table helps.
A good Calendar table:
- has a Date column with every date in the range
- includes Year, Month, MonthName, Quarter, etc.
- is marked as a date table in Power BI
Once related:
- Calendar[Date] → Sales[Date]
You can then analyse sales by:
- Year, Month, Quarter, Weekday
without messy manual columns.
11) Common relationship problems (and fixes)
Problem 1: “This relationship can’t be created”
Common causes:
- Data types don’t match (Text vs Whole Number)
- One side contains blanks or duplicates where it should be unique
Fix:
- Ensure both columns are the same data type
- Clean the key columns in Power Query
- Remove duplicates from the dimension table keys
Problem 2: Totals look too high (double counting)
Common causes:
- many-to-many relationship
- wrong table used for slicing
- fact-to-fact relationships
Fix:
- Use dimension tables for slicing (Customers, Products, Calendar)
- Avoid linking two fact tables directly
- Build a star schema
Problem 3: Filters don’t work as expected
Common causes:
- wrong filter direction
- relationship inactive
- multiple paths between tables
Fix:
- Use Single direction dimension → fact
- Check the relationship is active
- Remove unnecessary relationships
Problem 4: “Ambiguous relationships” warning
This happens when there are multiple ways to filter from one table to another.
Fix:
- Remove one of the paths
- Or set one relationship inactive and use DAX when needed
- Avoid “Both” filter direction unless you need it
12) Relationships vs Merge in Power Query: when to choose each
Use Relationships when:
- you want flexible reporting
- you want to slice measures by fields in different tables
- you have a classic fact + dimensions scenario
- you want a scalable model
Use Merge when:
- you need to add a lookup value during cleaning (like category)
- you want a single table output for export
- you need to shape data before it’s ready for the model
- the relationship key is messy and needs standardising first
In many reports, you do both:
- Power Query cleans and shapes
- Relationships connect tables for analysis
13) Best practice model checklist (quick)
- One main fact table (or a few, carefully designed)
- Dimension tables with unique keys
- Single-direction relationships from dimension to fact
- A proper Calendar table related by Date
- Avoid many-to-many unless you understand the reason
- Avoid fact-to-fact links
- Hide technical key columns from the report view (optional)
- Use measures for calculations rather than calculated columns where possible
Conclusion
Relationships are the heart of a good Power BI model. They let you keep tables separate and still analyse everything together. With a clean star schema, one-to-many relationships, and sensible filter directions, your measures will work correctly and your reports will be easier to maintain.
If you’d like hands-on guidance building data models, setting up relationships, and avoiding common mistakes, these courses will help:
.png){kind=small}
