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

1. What Is Data Modeling?
2. Data modeling is how you organize tables in Power BI so reports are accurate and fast
3. A good model keeps data clean, calculations correct, and DAX formulas simple
4. Two places where modeling happens in Power BI:
5. Power Query - where you load and clean tables

6. Model View - where you connect tables using relationships

7. SQL Joins

8. A join combines two tables based on a shared column (called a key)

9. In Power BI, joins are done in Power Query under Home > Merge Queries

The 6 Join Types

• INNER JOIN - returns only rows that match in both tables

• Example: customers who have placed an order (customers with no orders are excluded)

• LEFT JOIN - returns all rows from the left table, and matched rows from the right

• Example: all customers, whether they ordered or not (blank if no order)

• RIGHT JOIN - returns all rows from the right table, and matched rows from the left

• Example: all orders, even if the customer record is missing

• FULL OUTER JOIN - returns all rows from both tables

• Example: full data audit - show everything whether it matches or not

• LEFT ANTI JOIN - returns left rows with no match in the right table

• Example: customers who have never placed an order

• RIGHT ANTI JOIN - returns right rows with no match in the left table

• Example: orders with no matching customer (orphan records)

• Power BI Relationships

• Relationships link tables in Model View without merging them

• Filters pass between related tables when slicers or visuals are used

Cardinality

• One-to-Many (1:M) - most common. One row in Table A matches many rows in Table B
• Example: one product appears in many sales rows

• Dimension table is always on the '1' side, fact table on the 'Many' side

• Many-to-Many (M:M) - many rows in both tables can match each other

• Example: students and courses (one student takes many courses, one course has many students)

• Avoid if possible - can cause double-counting in measures

• One-to-One (1:1) - each row in Table A matches exactly one row in Table B

• Example: Employee table and EmployeeDetails table

Cross-Filter Direction

• Single - filters flow from the '1' side to the 'Many' side only (recommended)
• Both - filters flow in both directions (use carefully, can cause errors)

Active vs Inactive Relationships

• Only one active relationship is allowed between two tables at a time
• Extra relationships can be inactive and used in DAX with USERELATIONSHIP()
• Example: Sales has OrderDate and ShipDate, both connected to a Date table
• OrderDate is active, ShipDate is inactive and activated in a DAX measure

How to Create a Relationship

• Method 1: In Model View, drag a column from one table onto the matching column in another
• Method 2: Home > Manage Relationships > New, then select both tables and key columns

1. Power BI Relationships
2. Relationships link tables in Model View without merging them
3. Filters pass between related tables when slicers or visuals are used

Cardinality

• One-to-Many (1:M) - most common. One row in Table A matches many rows in Table B
• Example: one product appears in many sales rows

• Dimension table is always on the '1' side, fact table on the 'Many' side

• Many-to-Many (M:M) - many rows in both tables can match each other

• Example: students and courses (one student takes many courses, one course has many students)

• Avoid if possible - can cause double-counting in measures

• One-to-One (1:1) - each row in Table A matches exactly one row in Table B

• Example: Employee table and EmployeeDetails table

Cross-Filter Direction

• Single - filters flow from the '1' side to the 'Many' side only (recommended)
• Both - filters flow in both directions (use carefully, can cause errors)

Active vs Inactive Relationships

• Only one active relationship is allowed between two tables at a time
• Extra relationships can be inactive and used in DAX with USERELATIONSHIP()
• Example: Sales has OrderDate and ShipDate, both connected to a Date table
• OrderDate is active, ShipDate is inactive and activated in a DAX measure

How to Create a Relationship

• Method 1: In Model View, drag a column from one table onto the matching column in another
• Method 2: Home > Manage Relationships > New, then select both tables and key columns
• Fact and Dimension Tables

Fact Table

• Stores measurable numbers: revenue, quantity, cost
• Each row = one transaction or event
• Usually large (millions of rows)
• Always on the 'Many' side of a relationship
• Example: Sales table with OrderID, Date, CustomerID, ProductID, Revenue

Dimension Table

• Stores descriptions: product names, customer details, dates
• Each row = one unique item
• Usually smaller (thousands of rows)
• Always on the '1' side of a relationship
• Example: Products table with ProductID, Name, Category, Brand

1. Schemas

Star Schema (Recommended)

• One fact table in the center, dimension tables connected around it
• Best performance in Power BI
• Simple to understand and write DAX for
• Example: Sales connected to Date, Products, Customers, Location

Snowflake Schema

• Like a star schema but dimension tables are split into sub-tables
• Example: Product > Sub-Category > Category (three tables instead of one)
• Slower in Power BI - flatten dimension tables where possible

Flat Table

• Everything in one big table, no relationships needed
• Easy to set up but causes data repetition and slow performance
• Only suitable for very small or prototype projects

1. Role-Playing Dimensions
2. When one dimension table is used more than once in the same model
3. Common example: a Date table used for both OrderDate and ShipDate
4. Power BI only allows one active relationship per table pair
5. Solution: make one relationship active, others inactive

6. Use USERELATIONSHIP() in DAX to switch on an inactive relationship in a measure

7. Common Modeling Issues

8. No Date table: creates problems with time intelligence functions - always create a dedicated Date table

9. Wrong cardinality: duplicates on the '1' side break the relationship - key column must be unique

10. Too many bidirectional filters: causes circular filter errors - use single direction by default

11. Flat table in a complex model: hard to maintain and slow - break it into fact and dimension tables

12. M:M without a bridge table: leads to double-counting - use a junction table to split into two 1:M links

Quick Summary

• Data modeling = organizing tables for accurate, fast Power BI reports
• 6 join types in Power Query: INNER, LEFT, RIGHT, FULL OUTER, LEFT ANTI, RIGHT ANTI
• Relationships link tables in Model View - use 1:M for most cases
• Joins merge tables physically; relationships filter between separate tables
• Fact tables hold numbers; dimension tables hold descriptions
• Star schema is the best structure for Power BI
• Role-playing dimensions use USERELATIONSHIP() for inactive relationships
• Avoid flat tables, wrong cardinality, and unnecessary bidirectional filters