Understanding Data Modeling in Power BI: Joins, Relationships and Schemas Explained
Source: Dev.to
Continuing from my previous article “How Excel Is Used in Real‑World Data Analysis,” I’m now exploring Power BI. The past two weeks have been eye‑opening – I’ve discovered that, while Excel remains vital, other tools can perform many of the same tasks (often better). Below are the concepts I’ve learned so far, which may help you on your own learning journey.
📊 What Is a Data Model?
- Data Model – a visual representation of how data will be organized and stored in a database. Think of it as a map that shows what data lives in each table and how the tables are related.
- Data Modeling – the process of creating that “map”: connecting data sources, defining relationships, and arranging everything into a structure that supports analysis.
I’ll illustrate these ideas using a sample dataset that contains four tables (Customers, Products, Stores, Sales) spread across separate worksheets in Excel.
📁 The Sample Dataset (Excel View)
A screenshot of the workbook would go here, showing the four worksheets.
The dataset is already clean, but if you need to tidy it up first, use Power BI’s Transform Data (Power Query) before building the model.
🛠️ Three Ways to Work with Data in Power BI
| View | How to Access | What It Does |
|---|---|---|
| Table View | Open a blank report → Get Data → select the Excel workbook | Loads the raw tables into Power BI. |
| Model View | Click the Model icon (highlighted in the screenshot) | Visual canvas for creating and managing relationships between tables. |
| Report View | Click the Report icon (the one above the Table‑View icon) | Build visualisations; you can also manage relationships from here. |
Tip: All three views are accessible from the ribbon or the left‑hand navigation pane.
🔗 Managing Relationships
- After loading the data, go to Model View (or use Manage Relationships from any view).
- Click Manage Relationships → a pop‑up appears with a green + New Relationship button.
- In the dialog, select the two tables you want to relate and choose the shared column (e.g.,
CustomerIDbetween Sales and Customers). - Repeat for the other table pairs.
The result is a Data Model that looks roughly like this:
Insert a diagram of the model showing Sales at the centre linked to Customers, Products, and Stores.
🔀 Joins vs. Relationships
| Concept | What It Does |
|---|---|
| Join | Combines rows from two tables into a single table (performed in Power Query via Merge Queries). |
| Relationship | Defines how tables are linked while keeping them separate; Power BI uses these links to filter and aggregate data automatically. |
🔧 Types of Joins in Power BI (Power Query → Merge Queries)
| Join Type | Description |
|---|---|
| Left Outer Join | Keeps all rows from the left table and matches rows from the right table. Unmatched rows return null. |
| Right Outer Join | Keeps all rows from the right table and matches rows from the left table. Unmatched rows return null. |
| Full Outer Join | Keeps all rows from both tables; unmatched rows return null. |
| Inner Join | Returns only rows that have matches in both tables (reduces row count). |
| Left Anti Join | Returns rows from the left table without a matching row in the right table. |
| Right Anti Join | Returns rows from the right table without a matching row in the left table. |
In the Merge Queries dialog, the bottom of the window shows how many rows were matched – a quick sanity check.
🔗 Types of Relationships in Power BI
-
One‑to‑Many (1:M) – One row in the primary table relates to many rows in the secondary table.
Example: Sales → Customers, Sales → Products, Sales → Stores. This is the most common relationship and enables smooth aggregation. -
Many‑to‑Many (M:M) – Multiple rows in one table relate to multiple rows in another.
Caution: Can cause calculation errors; avoid when possible. -
One‑to‑One (1:1) – One row in a table relates to exactly one row in another table. Rare but useful for splitting a large table into logical parts.
-
Active vs. Inactive – You can define multiple relationships between the same two tables. Power BI uses the active relationship by default. To use an inactive one, you must reference it explicitly (e.g., with
USERELATIONSHIP).
Cardinality & Cross‑Filter Direction
| Property | Explanation |
|---|---|
| Cardinality | Describes the relationship type (1:M, M:M, 1:1). |
| Cross‑filter direction | Determines how filters flow between related tables. |
| Single direction | Filters flow only from the “one” side to the “many” side (or as defined). |
| Bidirectional | Filters flow both ways, useful for certain calculations but can impact performance. |
⭐ Star Schema
A Star Schema is a data‑model design where a central fact table (e.g., Sales) is surrounded by multiple dimension (lookup) tables (e.g., Customers, Products, Stores).
- Fact table stores transactional data (sales amounts, quantities, dates).
- Dimension tables store descriptive attributes (customer name, product category, store location).
Our model follows this pattern: Sales is the fact table, linked to three dimension tables.
🚀 Takeaways
- Data Modeling is the foundation for any robust analysis in Power BI.
- Relationships keep tables separate but enable powerful, automatic filtering.
- Joins are useful when you need a single combined table for further transformation.
- Understanding cardinality, cross‑filter direction, and active vs. inactive relationships lets you fine‑tune performance and accuracy.
- Designing a Star Schema simplifies reporting and improves query speed.
Feel free to experiment with the sample dataset, try different join types, and play with relationship settings. The more you practice, the more intuitive Power BI becomes!
Happy analyzing!
Data Modeling Overview
- Lookup tables – customers, products, stores
- Central (Fact) table – stores measurable (quantitative) data such as quantity sold, price, stock levels, etc.
The lookup tables are also called dimension tables because they provide context for the facts. They contain descriptive (qualitative) information such as names, cities, categories, email addresses, and so on.
Snowflake Schema
A snowflake schema is a variation of the star schema where dimension tables are further normalized into sub‑dimension tables. In practice, you extend the dimension tables of a star schema to create these additional layers.
Flat Table (DLAT)
A flat table (or DLAT – Data Lake Access Table) combines everything that would normally be split between fact and dimension tables into a single table.
Example: a single Excel worksheet that holds all business information and acts as its own database.
Key Points to Remember
- Clean your data first – use Power Query (or any ETL tool) before building the model.
- Prefer a star schema – it makes relationships easy to understand and helps avoid many‑to‑many joins, which can complicate calculations and introduce errors.