You've loaded your data into Power BI. You've got the tables. Now what are you expected to do?
Here's the thing most beginners skip: how your tables connect to each other is more important than the data inside them. A poorly structured model will give you wrong numbers, slow reports and a difficult time every time you try to add a new chart.
This week we're covering the foundation of Power BI, data modeling. Specifically: what kinds of tables exist, how schemas work, what relationships are and what joins are doing behind the scenes.
One thing that took me a while to get in Power BI is that loading data is only half the job. The other half is telling Power BI how your tables talk to each other. That's what schemas, relationships and joins are about.
The Problem I hadn't anticipated
When I first loaded data into Power BI, I had three separate tables; customers, products and sales. I could build charts from each one individually, but the moment I tried to combine them like "show me sales by customer city", things either broke or gave me numbers that made no sense.
Turns out I had no relationships set up. Power BI had no idea how those three tables were supposed to connect. That's what I got to learn.
Fact Tables vs Dimension Tables
Every Power BI model has two kinds of tables doing different jobs.
Fact tables store your actual transactions - every sale, every order, every event. They're usually huge and full of numbers and IDs.
Dimension tables give those numbers context. Your Customers, Products and Dates tables are dimension tables (fewer rows but more descriptive columns). They answer the who, what and when behind every fact.
The Star Schema
Power BI is basically built for this layout. One fact table sits in the middle, dimension tables connect around it. For instance:
DIM_Date
|
DIM_Customer — FACT_Sales — DIM_Product
|
DIM_Store
Filters flow from the outside in, queries run fast and the model stays easy to read. There's a variation called the Snowflake Schema where you break dimension tables into sub-tables, but in Power BI it adds complexity without much payoff. Star schema is the standard.
Snowflake Schema
The snowflake schema extends the star by breaking dimension tables into sub-tables. For example, instead of one DIM_Customer table with a City and Country column, you'd have a separate DIM_City table linked to a DIM_Country table.
Relationships
A relationship is the link between two tables through a shared column.
- Primary Key (PK) - uniquely identifies every row in a table. No duplicates.
- Foreign Key (FK) - that same ID living in another table as a reference.
The most common type is One-to-Many (1:N) - one customer, many orders. One product, many sales. The dimension table always sits on the "one" side, the fact table on the "many" side.
When you have a situation where many rows in Table A relate to many rows in Table B - like students and courses, that's a Many-to-Many relationship. Power BI doesn't handle this cleanly on its own so you use a bridge table in between, which breaks it into two clean one-to-many links.
Cross-Filter Direction
When you click on something in a visual, other visuals filter too. That behaviour is controlled by cross-filter direction.
Single direction - filters flow from dimension into fact only. This is the default and what you should stick with most of the time.
Bidirectional - filters travel both ways. Useful in specific cases but can slow things down and cause circular logic if overused.
Active vs Inactive Relationships
You can have more than one relationship between two tables but only one can be active at a time. A common example - a Sales table with both an OrderDate and a ShipDate, both linking to the same date table. One relationship is active by default, the other sits inactive until you call it explicitly in DAX using USERELATIONSHIP().
Joins
This is what Power BI does behind the scenes when combining tables in a visual.
Inner join - only rows with a match in both tables come through. Unmatched rows get dropped silently, which is why some totals can look off unexpectedly.
Left join - all rows from the left table come through, matched or not. Unmatched rows just show blank on the right side. This is what Power BI uses most of the time when pulling from a dimension into a fact table.
Cross Join - every row in Table A paired with every row in Table B. Rarely useful; produces enormous result sets.
Little hacks
- ✅ Use Star Schema whenever possible
- ✅ Keep dimension tables on the "one" side, fact tables on the "many" side
- ✅ One active relationship per table pair
- ⚠️ Avoid bidirectional filtering unless you have a specific reason
- ⚠️ Resolve Many-to-Many with a bridge table
Wrapping Up
Data modeling isn't the flashy part of Power BI, but it's the part that makes everything else work. Get your schema right, define clean relationships and your reports will be fast, your numbers accurate and your filters will just work.
If you have questions about anything covered here, drop them in the comments!
Top comments (0)