Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

By Tarus Kigen
Student Analyst*

Hello there. My name is Tarus, I thought Power BI was just about dragging colorful charts onto a canvas. I was wrong.

After getting lost in a mess of incorrect totals and weird filters, I realized the most important thing isn't visuals—it's data modeling. If your model is wrong, nothing works. So, I spent my week breaking down joins, relationships, and schemas. Let me share what I learned in simple terms.

What is Data Modeling?

Imagine building a house. You don't throw bricks, wood, and glass into a pile and call it a home. You organize them into walls, floors, and a roof. Data modeling is the same: organizing your raw data into a logical structure that Power BI can understand quickly and correctly.

Good data modeling = Fast reports + Correct numbers + Happy boss.

SQL Joins vs. Power BI Relationships (Key Difference!)

This confused me the most. In SQL databases, joins physically combine two tables into one new table. In Power BI, relationships leave the tables separate but "connect" them temporarily when you make a visual.

• Join (Power Query): Permanently merges columns from two tables.
• Relationship (Model View): A flexible link that respects table independence.

You use joins when cleaning data in Power Query. You use relationships when building models in Model View.

All SQL Joins Explained (As I Learned in Power Query)

I practiced these using Merge Queries in Power Query Editor (Home → Combine → Merge Queries). Here is my cheat sheet using two imaginary tables:

Table A: Students (StudentID, Name)

Table B: Grades (StudentID, Score)

1. INNER JOIN

"Give me students who have grades."

• Result: Only rows where StudentID exists in both tables.
• Real life: Listing only active employees who already completed training.
• Diagram: Two overlapping circles → only the middle.

2. LEFT OUTER JOIN

"Give me ALL students, plus grades if they exist."

• Result: Every row from left table (Students). Grades = NULL if missing.
• Real life: Customer list with order amounts (show customers even if they never ordered).
• Diagram: Left circle completely + overlapping middle.

3. RIGHT OUTER JOIN

"Give me ALL grades, plus student names if they exist."

• Result: Every row from right table (Grades). Name = NULL if missing.
• Real life: Transaction list showing unknown user IDs. (Honestly? You can just swap tables and use LEFT JOIN).

4. FULL OUTER JOIN

"Everyone and everything, matched where possible."

• Result: All rows from both tables. NULLs where no match.
• Real life: Merging two customer lists from different acquisitions.
• Diagram: Both circles fully merged.

5. LEFT ANTI JOIN

"Students who have NO grades."

• Result: Only rows in left table with no match in right.
• Real life: Finding products that were never sold.
• Diagram: Left circle minus the middle overlap.

6. RIGHT ANTI JOIN

"Grades with no student record."

• Result: Orphaned records in the right table.
• Real life: Detecting database integrity issues.

Where to make joins in Power BI:

Power Query Editor → Select first table → Merge Queries → Select second table → Choose join kind.

Power BI Relationships (Model View)

After loading tables, go to Model View (left sidebar). Drag a field from one table to another to create a relationship. Here is what each setting means:

Cardinality (1:M, M:M, 1:1)

• 1:M (One to Many): One product has many sales. This is 90% of your relationships. One row in the "one" side filters many rows on the "many" side.
• 1:1: One user has one passport. Rare. Use if splitting a wide table into two thin ones.
• M:M (Many to Many): Students have many classes, classes have many students. Warning: Use carefully, or totals will double.

Cross-Filter Direction

• Single (Default): Filters flow from "one" side to "many" side. You pick a date table, it filters sales.
• Both: Filters flow both ways. Only use when you know what you are doing (otherwise, ambiguous paths occur).

Active vs. Inactive Relationships

You can have multiple relationships between two tables (e.g., Sales table has OrderDate and ShipDate both linked to a Date table). But only one active at a time. Use USERELATIONSHIP in DAX to activate an inactive one.

Fact vs. Dimension Tables (The Golden Rule)

After my week, I finally understood this:

Fact Table	Dimension Table
Contains measurements (Sales Amount, Quantity)	Contains descriptions (Product Name, Customer City)
Changes often (every transaction)	Changes slowly (customer address)
Has foreign keys (ProductID, CustomerID)	Has primary keys (ProductID)
Example: Sales	Example: Products, Customers

Rule: Fact tables are at the "many" side of relationships. Dimensions are at the "one" side.

Schemas: Star, Snowflake, and Flat Table (DLAT)

1. Star Schema (The Winner)

One fact table in the middle, dimension tables directly connected like a star.

• Why I love it: Simple, fast, easy for beginners.
• Use case: Most business reports (sales, inventory, HR).

2. Snowflake Schema

Dimensions are further normalized into sub-dimensions (e.g., Product → Category → CategoryType).

• Why avoid as a beginner: Slower, more complex.
• Use case: Huge enterprise databases where storage is critical.

3. Flat Table (Denormalized, aka DLAT – "Don't Look At That")

One giant table with everything (fact + dimensions combined).

• Why not: Huge file size, repeating data, difficult filtering.
• Use case: Simple exports for small data (<100k rows). I started here and regretted it.

Role-Playing Dimensions

A date table that filters sales by OrderDate, then by ShipDate, then by DueDate. Same dimension table, used many ways.

How to create: Build one Date table, create inactive relationships for secondary dates. Use measures like:

Sales by Ship Date = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Date[Date]))

Common Modeling Issues I Faced (And Fixed)

1. Bidirectional filters causing ambiguity → Set cross-filter to Single.
2. Many-to-many giving wrong totals → Add a bridge table (e.g., StudentClass).
3. Blank rows appearing in slicers → Fix foreign key mismatches (orphan rows).
4. Circular dependencies → Remove redundant relationships.

Step-by-Step: Where to Create These in Power BI

Task	Where in Power BI
Merge two tables (JOIN)	Power Query Editor → Merge Queries
Create a relationship	Model View → Drag field to field
Change cardinality	Model View → Manage Relationships → Edit
Activate/inactivate relationship	Model View → Click line → Uncheck "Active"
View schema	Model View (zoom out to see all tables)
Create a flat table	Power Query → Append Queries (stack) or Merge (widen)

Final Advice

Start with a star schema: one fact table, a few dimensions. Use 1:M relationships with single filter direction. Avoid many-to-many. Use joins only in Power Query to clean data (e.g., adding lookup values), then use relationships for reporting.

After one week, my reports no longer lie. Yours will too. Happy modeling.

*— Tarus Kigen, Student Analyst.