Data Modelling, Relationships And Joins In Power BI

Introduction

I used to think Power BI was mainly about dragging and dropping charts. If the data loaded successfully, I assumed the report would work.

Then I built a dashboard that took 45 seconds to refresh, and my DAX formulas started returning unexpected results.

That’s when I realized something important:

The problem wasn’t the charts. It was the model.

Data modelling in Power BI is about far more than simply “organizing tables.” The data model determines how filters flow through a report, how efficiently the model performs, and whether your calculations return reliable results.

In this article, I’ll walk through what I learned while fixing my own data models, including schemas, fact and dimension tables, relationships, filter direction, and the often-confusing difference between Power Query joins and Power BI model relationships.

Data Modelling in Power BI

At its core, data modelling is about deciding how your tables should interact.

I was working with a retail dataset containing customers, products, and sales. My first instinct was to load everything into one large table.

The Problem

It quickly became a mess.

“Alice” appeared thousands of times. “Nairobi” appeared thousands of times. The file became unnecessarily large, and writing DAX became more difficult because I had to work with repeated customer and location information just to calculate sales.

The Solution

I learned that a well-designed model separates context—who, what, and where—from events—how much or how many.

That reduces redundancy, simplifies DAX, improves performance, and makes the model easier to scale.

Flat Table

My First Attempt: The “Excel” Approach

I initially combined everything into one wide table:

Customer ID | Date | Customer | Product | Sales | Category | Location

For example:

Customer ID | Date       | Customer | Product | Category    | Location | Quantity | Sales
1001        | 01/09/2026 | Alice    | Laptop  | Electronics | Nairobi  | 1        | 80,000
1002        | 01/09/2026 | John     | Phone   | Electronics | Kisumu   | 2        | 60,000

The Verdict

It was easy to import, but difficult to maintain.

If Alice moved from Nairobi to Mombasa, for example, I could potentially need to update thousands of rows containing her information.

A flat table can work well for small and simple datasets, but as the model grows, duplication quickly becomes a problem.

Star Schema

The Fix

I switched to a Star Schema.

Instead of keeping everything in one table, I placed a Fact Table containing the measurable events at the centre and surrounded it with Dimension Tables containing the descriptive context.

For example:

• Fact Sales: Customer Key, Product Key, Sales Amount
• Dim Customer: Customer Key, Name, City
• Dim Product: Product Key, Name, Category

Why It Worked

The structure was easier to understand, easier to maintain, and better suited to analysis in Power BI.

Instead of repeatedly storing customer and product information alongside every transaction, I could store that descriptive information once and connect it to the sales data through relationships.

The result was a cleaner model and simpler DAX.

Snowflake Schema

The Over-Engineering Trap

At one point, I tried to be “too proper” by further splitting my dimension tables.

For example, instead of keeping the product category within the product dimension, I created a separate DimCategory table that connected to DimProduct, which then connected to FactSales.

The structure worked, but it introduced additional relationships and made the model more complicated.

For my use case, the Star Schema was simpler and easier to work with.

The lesson I took away was simple: don’t introduce additional layers of normalization unless there is a clear reason to do so.

Fact Tables and Dimension Tables

Once I separated my tables, I needed to understand the role each type of table played.

Fact Tables

Fact tables contain the events or transactions being analysed.

They typically contain measurable values such as Sales and Quantity, along with the foreign keys needed to connect those transactions to dimension tables.

They answer questions such as:

• How much?
• How many?
• How often?

For example, FactSales might contain:

Customer Key | Product Key | Date Key | Quantity | Sales Amount

Dimension Tables

Dimension tables provide the descriptive context used to analyse the facts.

They typically contain attributes such as names, categories, locations, and dates.

They answer questions such as:

• Who?
• What?
• Where?
• When?

For example, DimCustomer might contain:

Customer Key | Customer Name | City

This separation between facts and dimensions is one of the foundations of a good Power BI model.

Grain or Granularity of a Fact Table

This was one of the concepts I found most important to understand.

Grain means defining exactly what one row in a fact table represents.

In my FactSales table, for example, I might define the grain as:

One row = one product sold in one order.

That definition matters.

If I start mixing rows representing “one product sold” with rows representing “one entire order,” my calculations can become inconsistent and potentially produce incorrect results.

The key lesson is simple:

Define the grain clearly and keep it consistent.

Relationships in Power BI

Once I had separated my tables, I needed a way for them to work together.

That’s where relationships came in.

One-to-One

One-to-one relationships are relatively uncommon in typical Power BI models.

They can be useful in specific situations, such as when a table is intentionally split into two related tables.

One-to-Many: The Standard Relationship

This is the relationship pattern I use most often.

For example:

• Dim Customer (1) connects to Fact Sales (*)
• One customer can have many sales transactions.
• The “one” side—the dimension—must contain unique keys.

This relationship allows a selection such as a customer, city, or other dimension attribute to filter the corresponding sales records.

Many-to-Many

This is where things can become more complicated.

I initially tried connecting FactSales directly to DimCategory, but the relationship did not behave the way I expected because the underlying data could involve multiple category associations.

The solution was to introduce a Bridge Table.

Instead of creating one direct many-to-many relationship, the bridge table allows the model to use two one-to-many relationships.

This provides a clearer and more controlled relationship structure.

Referential Integrity

I also learned the importance of referential integrity.

For example, if FactSales contains a CustomerID that does not exist in DimCustomer, Power BI may return a (Blank) member when the relationship is used.

That is a modelling issue, but it is also a data-quality issue.

A good model therefore depends not only on how tables are structured, but also on the quality and consistency of the underlying keys.

Active and Inactive Relationships

Another concept I had to understand was active versus inactive relationships.

My FactSales table contained both OrderDate and ShipDate, while my model had a DimDate table.

I could not simply make both relationships active at the same time in the way I wanted.

Instead, I kept OrderDate as the active relationship and used DAX when I needed to analyse sales based on ShipDate.

This allowed the same date dimension to support different date-based analyses without creating unnecessary complexity in the model.

Filter Direction

This was another area that explained why some of my slicers worked as expected while others did not.

Single Direction

With a single-direction relationship, filters generally flow from the Dimension → Fact.

For a standard Star Schema, this is usually the safest and simplest approach.

For example:

Dim Customer → Fact Sales

Selecting a customer filters the relevant sales records.

Bidirectional

With bidirectional filtering, filters can flow in both directions between related tables.

Although this can be useful in specific scenarios, I found that relying on it unnecessarily could introduce ambiguous filter paths and make the model harder to understand and troubleshoot.

It could also affect performance.

My Rule

Use single-direction filtering by default, and introduce bidirectional filtering only when there is a clear modelling requirement for it.

Joins in Power Query

Sometimes I needed to change or combine data before it entered the Power BI data model.

That’s where Power Query Merges became useful.

A merge allows me to combine queries based on matching columns.

Join Type	Records Retained	Example
Left Outer	All records from the left table plus matching records from the right	Find all customers, including those without orders
Right Outer	All records from the right table plus matching records from the left	Find all orders, including those without matching customers
Full Outer	All records from both tables	Identify matched and unmatched records from both sides
Inner	Only matching records	Find customers who placed orders
Left Anti	Records in the left table with no match in the right	Find customers who never placed an order
Right Anti	Records in the right table with no match in the left	Find orders without a matching customer

These joins are particularly useful when I need to transform or enrich the data before it is loaded into the model.

Power Query Joins vs. Power BI Relationships

This was probably one of the most confusing distinctions for me to understand.

A Power Query Merge and a Power BI relationship both connect data, but they serve different purposes.

Power Query Merge

A merge combines data during the data preparation stage.

For example, I might merge a customer table with another source to bring additional attributes into the customer query.

The resulting columns become part of the transformed data.

Power BI Relationship

A relationship connects tables within the data model.

Instead of physically combining the tables, the relationship allows Power BI's model engine to understand how the tables are connected and how filters should propagate between them.

My Rule

The distinction that helped me was:

Merge for enrichment. Relate for analysis.

If I need to bring additional columns into a table as part of data preparation, a Power Query Merge may be appropriate.

If I simply need separate tables to work together during analysis, a model relationship is usually the better approach.

My Recommended Power BI Model

After working through the different approaches—and making a few mistakes along the way—this is the architecture I generally recommend:

1. Schema: Star Schema
2. Relationships: One-to-Many (1:*) from Dimensions to Facts
3. Filter Direction: Single Direction

Why?

Because the model is:

• Easier to understand
• Easier to maintain
• More predictable when writing DAX
• Well suited to analytical reporting
• Less likely to develop unnecessary relationship complexity

For example:

Dim Product  1 → *  Fact Sales
Dim Date     1 → *  Fact Sales
Dim Customer 1 → *  Fact Sales

With this structure, a straightforward measure such as:

SUM(FactSales[Sales Amount])

can work naturally with filters coming from the related dimension tables.

Conclusion

Data modelling isn’t just about making a Power BI report work.

It’s about building a model that remains accurate, understandable, and scalable as the data and reporting requirements grow.

I started with a flat table because it seemed simple. I then experimented with more complex structures before understanding why the Star Schema is so widely used for analytical models.

The biggest lesson I took away is this:

A good Power BI report starts with a good data model.

When the model is structured properly, relationships become easier to manage, DAX becomes simpler, filters behave more predictably, and the entire report becomes easier to maintain.

The charts are what users see.

The model is what makes them work.