Power BI Data Modelling, Relationships and Joins:
When I first started working with Power BI, I thought building a report was mainly about choosing charts. I quickly learned that the real work happens behind the visuals.
A good Power BI report starts with a good data model.
1._ Data Modelling in Power BI_
Data modelling is the process of organising tables and defining how they relate to each other. A good model improves DAX, performance, scalability, reporting and maintenance.
Flat Table
Everything is stored in one large table.
Sales → Customer + Product + Date + Location + Amount
Pros: Simple and easy to understand.
Cons: Repeated data, larger models and harder maintenance.
It works for small datasets, but becomes inefficient as data grows.
Star Schema
A central fact table connects directly to several dimension tables.
DimCustomer → FactSales ← DimProduct
↑
DimDate
This is usually my preferred approach because it is simple, fast and DAX-friendly.
Snowflake Schema
Dimensions are further split into related tables.
DimCategory → DimProduct → FactSales
DimCountry → DimCustomer → FactSales
It reduces duplication but creates more relationships and complexity. It can be useful for large, highly normalised datasets, but I would normally prefer a star schema in Power BI.
- Fact and Dimension Tables
A fact table records business events and numeric values.
Example:
FactSales: SalesID, CustomerID, ProductID, DateID, Quantity, Revenue
A dimension table describes those events.
DimCustomer: CustomerID, Name, Region
DimProduct: ProductID, ProductName, Category
DimDate: DateID, Date, Month, Year
The most important concept is grain—what one row represents. For example, one row in FactSales might represent one product sold in one transaction.
3._ Relationships_
A relationship tells Power BI how tables are connected.
The common types are:
1: One-to-Many:* one customer can have many sales. This is the normal star-schema relationship.
1:1 One-to-One: each record matches one record. Useful only in specific cases.
: Many-to-Many:* multiple records match multiple records. Use carefully because it can create ambiguous filtering.
Usually, DimCustomer[CustomerID] is unique, while FactSales[CustomerID] can appear many times. The first is a primary key; the second is a foreign key.
Active relationships are used by default. Inactive relationships can be activated in DAX when needed.
- Filter Direction
With single-direction filtering, a selection in DimProduct filters FactSales.
DimProduct → FactSales
Bidirectional filtering allows filters to travel both ways, but it should be used carefully. It can create ambiguous filter paths and unnecessary complexity.
- Joins in Power Query
A join combines tables using Merge Queries.
Using Customers and Orders:
Join Records retained
Left Outer All Customers + matching Orders
Right Outer All Orders + matching Customers
Full Outer All records from both
Inner Only matching records
Left Anti Customers without Orders
Right Anti Orders without Customers
Joins are useful when you actually need to bring columns or rows together during data preparation.
- Joins vs Relationships
This distinction confused me at first.
A Power Query merge physically combines data before it enters the model.
A Power BI relationship does not combine tables. It simply tells Power BI how tables should filter each other.
Therefore, I would avoid excessive merging. Keeping FactSales, DimCustomer, DimProduct and DimDate separate usually produces a cleaner model.
- My Recommended Model
For most business intelligence projects, I would choose a Star Schema:
Dimensions → Fact Table
I would normally use 1: relationships*, with dimensions on the “1” side and facts on the “*” side, using single-direction filtering.
Why?
It gives me better performance, simpler DAX, less redundancy, easier reporting, clearer relationships and easier maintenance.
Power BI is not just about making beautiful dashboards. A beautiful dashboard built on a poor model will eventually become a painful dashboard to maintain.
Build the model well, and the report becomes much easier.
Top comments (0)