Understanding Data Modelling, Relationships and Joins in Power BI

Introduction

When working with Power BI, importing data is only the beginning. The real challenge comes when you have multiple tables and need to make sure they work together correctly.

This is where data modelling, relationships, and joins become important.

For example, imagine a retail company that wants to analyse its sales. It may have information about customers, products, dates, locations, and individual sales transactions stored in different tables.

If these tables are not structured properly, reports can become difficult to build and DAX calculations may produce unexpected results.

In this articles, I will explain how data modelling works in Power BI, the different modelling schemas, relationship and cardinality, filter directions, and the different types of joins available in Power Query.

1. What is Data Modelling in Power BI?

Data Modelling is the process of organising tables, columns, and relationships so that data can be analysed correctly in Power BI.

Instead of putting every piece of information into one large table, we can separate the data into tables based on their purpose and then connect them using relationships.

A good data model is important because it can help with:

• Better report performance
• Simpler DAX calculations
• Easier filtering
• Reduced data redundancy
• Easier maintenance
• Scalability as the amount of data increases
• Better understanding of the report structure

For example, a sales model might contain:

• FactSales
• DimCustomer
• DimProduct
• DimDate
• DimLocation

These tables can then be connected using keys.

2. Data Modelling Approaches
There are several ways of structuring data.
Three common approaches are flat table, star schema, and snowflake schema

Flat Table
A flat table keeps most or all information in a single table.

For example:

SalesID	Customers	Product	Category	Location	Date	Quantity	Revenue
S001	Amina	Maize Flour	Food	Nairobi	10/09/26	2	300
S002	Brian	Cooking Oil	Food	Mombasa	10/09/26	1	250
S003	Carol	Soap	Household	Nairobi	11/09/26	3	450

┌─────────────────────────────┐
│         Sales Table         │
├─────────────────────────────┤
│ SaleID                      │
│ Customer                    │
│ Product                     │
│ Category                    │
│ Location                    │
│ Date                        │
│ Quantity                    │
│ Revenue                     │
└─────────────────────────────┘

Advantages

• Simple to understand
• Easy to create
• Useful for small datasets
• Requires fewer relationships

Disadvantages

• Repeated information creates data redundancy
• The table can become very large
• Changes to descriptive information may need to be made in many rows
• Can make the model harder to maintain
• It may not be ideal for large analytical solutions.

A flat table can be useful for a small project or simple anaylsis, especially when the dataset does not contain many different entities.

Star Schema
A star schema separates business transactions from descriptive information.

The central table is normally a fact table, while the surrounding tables are dimension tables

For example:

                 ┌───────────────┐
                 │ DimCustomer   │
                 └───────┬───────┘
                         │
                         ▼
┌───────────────┐  ┌───────────────┐  ┌───────────────┐
│  DimProduct   │──│   FactSales   │──│    DimDate    │
└───────────────┘  └───────┬───────┘  └───────────────┘
                           │
                           ▼
                    ┌───────────────┐
                    │  DimLocation  │
                    └───────────────┘

The fact table stores events or transactions, while the dimension tables provide context about those events.

Advantages

• Easy to understand
• Works well with Power BI
• Usually produces simpler DAX
• Makes filtering easier
• Reduces unnecessary repetition
• Easier to maintain and expand

Disadvantages

• Requires relationships between tables
• More tables can initially seem complicated
• Poorly designed relationships can cause incorrect results

For many Power BI reporting projects, a star schema is a practical way of organising analytical data.

Snowflake schema
A snowflake schema is similar to a star schema, but some dimension tables are further divided into related tables

For example, instead of storing the category directly in DimProduct, we could create a separate DimCategory table.

                 ┌─────────────────┐
                 │   DimCategory   │
                 └────────┬────────┘
                          │
                          ▼
                 ┌─────────────────┐
                 │   DimProduct    │
                 └────────┬────────┘
                          │
                          ▼
                 ┌─────────────────┐
                 │    FactSales    │
                 └─────────────────┘

Advantages

• Reduces repeated information
• Can be useful when dimensions have multiple levels
• Can represent complex organizational structures

Disadvantages

• More tables and relationships
• More complex model
• Can make DAX and filtering harder to understand
• More joins/relationships may be required

A snowflake schema can be useful when the source data naturally contains hierarchical dimensions, but unnecessary splitting can add complexity

3. Fact Tables and Dimension Tables
Understanding fact and dimension tables is one of the most important parts of data modelling.

Fact Table
A fact table normally contains business events or transactions.

For a retail company, FactSales could contain:

SaleID	CustomerID	ProductID	DateID	Quantity	Revenue
S001	C001	P001	D001	2	300
S002	C002	P002	D001	1	250
S003	C001	P003	D002	3	450

The table contains numerical values that can be used for calculations, such as:

• Quantity
• Revenue
• Cost
• Profit
• Discount

What is grain?
The grain tells us what one row in the fact table represents.

For example:
One row in the FactSales represents one product sold in one sales transaction.

Defining grain before creating a model is important because it helps determine what information belongs in the fact table.

Dimension Table
Dimension tables contain descriptive information that gives context to the facts.

For example:
DimCustomer

CustomerID	CustomerName	City
C001	Amina	Nairobi
C002	Brian	Mombasa

DimProduct

ProductID	ProductName	Category
P001	Maize Flour	Food
P002	Cooking Oil	Food

DimDate

DateID	Date	Month	Year
D001	10/09/26	September	2026
D002	11/09/26	September	2026

Dimension tables answer questions such as:

• Who brought the product?
• What product was sold?
• When was it sold?
• Where did the sale happen?

The fact table tells us what happened, while dimensions help explain who, what, when, and where.

4. Relationships in Power BI
A relationship connects tables through a common column.

For example, CustomerID can connect DimCustomer to FactSales.

Dimension Table	Key	Fact Table	Foreign Key	Relationship
DimCustomer	CustomerID	FactSales	CustomerID	1:*
DimProduct	ProductID	FactSales	ProductID	1:*
DimDate	DateID	FactSales	DateID	1:*
DimLocation	LocationID	FactSales	LocationID	1:*

One-to-One (1:1)

One row in one table corresponds to one row in another table.

| Table A | Relationship | Table B |
|---|---|---|
| A001 | 1 : 1 | A001 |
| A002 | 1 : 1 | A002 |
| A003 | 1 : 1 | A003 |

One-to-Many (1:*)

One row in one table can be related to many rows in another table.

| DimCustomer | Relationship | FactSales |
|---|---|---|
| C001 | 1 : * | C001 |
| C001 | 1 : * | C001 |
| C001 | 1 : * | C001 |
| C002 | 1 : * | C002 |
| C002 | 1 : * | C002 |

This relationship can be useful when information belonging to the same entity has been separated into two tables.

However, if the tables always need to be used together, it may be worth considering whether they really need to remain separate.

Many-to-Many (:)

Many rows in one table can be related to many rows in another table.

| Students | Relationship | Courses |
|---|---|---|
| S001 | * : * | C001 |
| S001 | * : * | C002 |
| S002 | * : * | C001 |
| S002 | * : * | C003 |
| S003 | * : * | C002 |

Many-to-many relationship should be designed carefully because they can make filtering and calculations more complicated.

Where possible, a bridge or intermediate table can help create a clearer model.

Examples:

• 1:1 → One employee ↔ One employee profile
• 1:* → One customer ↔ Many sales
• : → Many students ↔ Many courses

6. Active and Inactive Relationships
Power BI can have relationships that are either active or inactive.

An active relationship is normally used automatically when filters and calculations are performed.

An inactive relationships exists in the model but is not automatically used for filtering.

For example, a sales table may contain:

• OrderDate
• ShipDate

Both dates could connect to the same DimDate table, but only one relationship can normally be active between the same two tables for a given path.

Inactive relationships can be used in specific DAX calculations when needed.

7.Filter Direction
Relationships also determine how filters move between tables.

Single-direction filtering
With single-direction filtering, a filter normally moves from the dimension table towards the fact table.

DimProduct  ───────────────→  FactSales
              Filter

Suppose I select **maize flour** in a report.

Power BI can use the relationship to filter fact sales and show only sales involving Maize Flour.

This is commonly used in star schemas because it creates a clear filtering path.

**Bidirectional filtering**
Bidirectional filtering allows filters to travel in both directions.

text
DimProduct ◄──────────────► FactSales
Filter

Although this can be useful in certain scenarios, it should be used carefully.

Using bidirectional relationships unnecessarily can create:

• Ambiguous filter paths
• Unexpected results
• More complicated models
• Difficulty troubleshooting calculations

For this reason, single-direction filtering is often preferred unless there is a clear reason to use both directions.

8. Joins in Power Query
Relationships are not the only way to work with multiple tables.

Power Query provides Merge Queries, which allows us to join tables based on matching columns.

Imagine we have:

Customers

CustomerID	CustomerName
C001	Amina
C002	Brian
C003	Carol

Orders

|CustomerID | OrderID |
|C001 | O101 |
|C002 | O102 |
|C004 | 0103 |

Here, CustomerID is the matching column.

Power Query provides several join types.

9.Left Outer Join
A left outer join keeps all rows from the first/left table and matching rows from the second/right table.

Expected result:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102
C003	Carol	null

Carol is retained even though she has no matching order.

This is useful when we want to keep every customer and see whether they have corresponding orders.

10.Right Outer Join
A right outer join keeps all rows from the second/right table and matching rows from the first/left table.

Expected results:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102
C004	null	O103

C004 is retained even though that customer does not exist in the Customers table.

11. Full Outer Join
A full outer join keeps all rows from both tables, whether or not there is a match.

Expected results:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102
C003	Carol	null
C004	Null	O103

This can be useful when identifying records that exist in one table but not the other.

12. Inner Join
An inner join keeps only records that have a match in both tables.

Expected results:

CustomerID	CustomerName	OrderID
C001	Amina	O101
C002	Brian	O102

C003 and C004 are removed because they do not have matching records on both sides.

13. Left Anti Join
A left anti join returns records that exist in the left table but have no matching record in the right table.

Expected result:

CustomerID	CustomerName
C003	Carol

This is useful when finding customers who have never placed an order.

14. Right Anti Join
A right anti join returns records that exist in the right table but have no matching record in the left table.

Expected result:

CustomerID	OrderID
C004	O103

This can help identify orders whose customer information is missing from the Customers table.

15. Power Query Joins vs Power BI Relationships
One thing I found important when learning Power BI that a join and a relationship are not the same thing.

Power Query Merge
A merge happens during the data preparation stage.

It can bring columns from one table into another table.

For example, we could merge Customers and Orders using CustomersID.

The resulting table contains information from both tables.

Power BI Relationship
A relationship happens in the data modelling stage.

It does not physically combine the tables.

Instead, Power BI keeps the tables separate and uses the relationship to allow filters and calculations to work across them.

Merge combines data. Relationship connects data.

When should I merge?
A merge can make sense when:

• You genuinely need columns from another table in the same table
• You are cleaning or preparing data
• The resulting structure is simpler and appropriate for the analysis.

However, excessive merging can create very wide tables, duplicate information, and make a model harder to maintain.

When should I use a relationship?
Relationships are useful when the tables represent different business entities.

For example:

And for the second illustration:

text
DimCustomer
DimProduct
DimDate
DimLocation
↓
FactSales

Keeping these tables separate makes the model easier to understand and allows dimensions to be reused for different analyses.

16. My Recommended Power BI Model
For a typical business intelligence project, I would generally structure the model around a star schema.

                    ┌─────────────────┐
                    │  DimCustomer    │
                    │   CustomerID    │
                    └────────┬────────┘
                             │ 1:*
                             ▼
┌─────────────────┐   ┌─────────────────┐   ┌─────────────────┐
│   DimProduct    │──►│    FactSales    │◄──│     DimDate     │
│   ProductID     │   │     SaleID      │   │     DateID      │
└─────────────────┘   │     Quantity    │   └─────────────────┘
                      │     Revenue     │
                      └────────┬────────┘
                               │ 1:*
                               ▼
                    ┌─────────────────┐
                    │   DimLocation   │
                    │   LocationID    │
                    └─────────────────┘

The reason I would use this structure is that it provides a good balance between simplicity, performance, scalability, and maintainability.

The fact table contains measurable business events, while the dimension tables provide descriptive information.

I would normally use:
- One-to-many relationships
- Dimension tables on the 1 side
- Fact tables on the * side
- Single-direction filtering from dimensions to facts
- Unique primary keys in dimension tables
- Foreign keys in fact tables

For example:

text
DimProduct [1] ───────── [*] FactSales

This allows selecting a product to filter the corresponding sales records.

A star schema also makes it easier to write and understand DAX measures because the model has clear relationships and predictable filter paths.

A flat table can still be useful for a small or simple dataset, while a snowflake schema can be appropriate when dimensions naturally contain several levels of hierarchy. However, adding unnecessary tables and relationships can increase model complexity.

Conclusion

Data modelling is an important part of building a Power BI solution. It is not simply about importing tables and creating charts. The way tables are structured and connected affects how easily we can analyse the data and how reliable our results will be.

Understanding fact tables, dimension tables, schemas, relationships, cardinality, filter direction, and joins makes it easier to build models that can handle real business questions.

One of the biggest distinctions to remember is the difference between a Power Query merge and Power BI relationship. A merge combines data during data preparation, while a relationship connects separate tables during modelling.

For many business intelligence projects, a well-designed star schema with clear one-to-many relationships and appropriate filter directions provides a strong foundation for building Power BI reports.

Ultimately, the goals of data modelling is simple: organise data in the way that makes analysis accurate, understandable, and easier to maintain