When I first opened Power BI, I thought it was just a prettier Excel. Then I hit data modelling and it looked me dead in the eye and said "not today."
Data modelling is the backbone of any Power BI report. It decides how your tables connect, how fast your visuals load, and whether your numbers are actually trustworthy. It's what allows Power BI to understand that a sale belongs to a product, a customer belongs to a region, and a date belongs to a calendar.
Without a proper model, your visuals might look impressive while quietly lying to you. Once you get it? Everything clicks. Let me break it down simply.
Relationships: Getting Your Tables to Actually Talk
A relationship connects two tables through a shared column called a key. If your Sales table and Products table both have a ProductID column, that's your link right there.
Every relationship starts with two important columns:
Primary Key (PK) – a column whose values are unique. Think ProductID in a Products table where every product appears once.
Foreign Key (FK) – a column that references the primary key from another table. Think ProductID in a Sales table where the same product
The primary key identifies the record. The foreign key points to it. Together, they allow Power BI to connect the dots.
| Type | What it means | Example |
|---|---|---|
| One-to-Many (1:*) | One row on one side, many on the other | One product → many sales |
| Many-to-Many (:) | Both sides have repeats | Students ↔ Courses |
Products Table ───────────────── Sales Table
┌─────────────┐ ┌─────────────┐
│ ProductID │──────── 1 : * ────│ ProductID │
│ ProductName │ │ SaleAmount │
│ Category │ │ SaleDate │
└─────────────┘ └─────────────┘
Relationships also have a filter direction.Think of it as a one-way street. Filters travel from the one side to the many side.
One thing I learned very quickly is that relationships matter more than you think. Without them, Power BI sees your tables as separate islands. That's why you might click a product in one visual and wonder why absolutely nothing happens in the others.
Star Schema vs Snowflake Schema
Your schema is the overall layout of your data model basically how all your tables are arranged and connected to each other.
Star Schema - The One You Want
┌─────────────┐
│ DimDate │
└──────┬──────┘
│
┌────────────┐ ┌──▼─────────┐ ┌─────────────┐
│DimCustomer │──│ FactSales │──│ DimProduct │
└────────────┘ └──┬─────────┘ └─────────────┘
│
┌──────▼──────┐
│ DimRegion │
└─────────────┘
One central fact table (your numbers in terms of sales amounts, quantities) with dimension tables around it (your context: dates, customers, products). Clean, fast, and exactly what Power BI is optimised for. DAX, Power BI's formula language, was designed with this layout in mind.
One thing that surprised me was how much Power BI prefers a star schema. At first, I wanted to keep breaking tables into smaller and smaller pieces because it felt more organised. But Power BI works best when dimensions stay relatively flat and connect directly to a central fact table. Fewer relationship paths mean faster calculations and fewer headaches when writing DAX.
Snowflake Schema - The Complicated One
┌────────────┐ ┌────────────┐ ┌────────────┐
│ DimProduct │──▶│ DimCategory│──▶│DimSuperCat │
└─────┬──────┘ └────────────┘ └────────────┘
│
┌─────▼──────┐
│ FactSales │
└────────────┘
This keeps splitting dimension tables into smaller sub-tables (e.g. Product → Category → Super Category). It looks tidy but makes your model slower and your DAX measures harder to write.
Joins: Combining Tables in Power Query
Joins happen in Power Query before data even reaches your model. You're deciding which rows from each table make the cut. The easiest way to picture this is with Venn diagrams:
| Join Type | What you get |
|---|---|
| Inner Join | Only rows that match on both sides |
| Left Outer Join | All left rows + right matches |
| Full Outer Join | Every row from both tables |
I once used the wrong join and ended up with sales figures in the millions when they should've been in the thousands. The data wasn't wrong I was just inviting the wrong rows to the party.
What Data Modelling Actually Taught Me
The biggest lesson wasn't how to create relationships or choose the correct join.
It was understanding that dashboards are only as good as the model underneath them.
A beautiful report built on a bad model will still give bad answers.
After working with relationships, schemas, joins, keys, and cardinality, I realised that data modelling isn't something you do before analysis. It is part of the analysis.
My biggest takeaways:
- Build your model before building visuals.
- Use a Star Schema whenever possible.
- Understand your keys before creating relationships.
- Be intentional with joins in Power Query.
- If your numbers look wrong, check the model first. The chart may be what people see, but the model is what makes it trustworthy.
Data modelling isn't the flashiest part of Power BI nobody's putting their model diagram on a mood board. But get it right, and your visuals, your numbers, and your whole report will thank you.
Top comments (1)
The bit about clicking a product and nothing else changing because the tables are still separate islands is exactly where Power BI stops being "pretty Excel" and starts being software design. A star schema around FactSales, with flatter DimDate, DimCustomer, DimProduct, and DimRegion tables, is not just tidier; it reduces ambiguity before DAX ever gets involved. From a founder/engineer angle, this is why dashboard work needs a modelling review before anyone argues about visuals: a wrong join that turns thousands into millions can create bad product or revenue decisions with a very polished chart.