DEV Community

Shadrack Tesot
Shadrack Tesot

Posted on

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

#datascience #sql #powerfuldevs #datastructures

When I started learning data analysis, I thought Power BI was mostly about dashboards and visuals. Clean charts, nice colors, maybe a few filters.

Then I hit data modeling.

That’s when things got real.

Because no matter how good your visuals look, if your data model is wrong, your insights will be wrong too. And in the real world wrong insights cost money.

What is Data Modeling?

Data modeling is how you structure, connect, and organize your data so that it can be analyzed correctly and efficiently.

In Power BI, data modeling happens after loading your data, and it determines:

  • How tables relate to each other
  • How filters behave
  • How calculations work

SQL Joins (The Foundation of Data Combination)

Before Power BI relationships, you need to understand joins. These are done in Power Query.

Joins

1. INNER JOIN

Inner Join
Returns only matching records in both tables.

Example:

  • Orders table
  • Customers table → Only customers who have placed orders

2. LEFT JOIN

Left Join

Returns all records from the left table, and matching ones from the right.

Example:
All orders, even if customer details are missing:**
You want to see:

3. RIGHT JOIN

Same as LEFT JOIN but reversed.

Example:
All customers, even if they haven’t placed orders.

4. FULL OUTER JOIN

Full Outer Join

Returns everything from both tables.

Example:

  • All customers
  • All orders
  • Whether they match or not

5. LEFT ANTI JOIN

Returns rows in the left table that don’t exist in the right.

Example:
Orders without matching customers.

6. RIGHT ANTI JOIN

Returns rows in the right table that don’t exist in the left.

Where to Create Joins in Power BI

  1. Go to Home → Transform Data (opens Power Query)
  2. Select your table
  3. Click Merge Queries
  4. Choose:
  • Second table
  • Matching column
  • Join type (Inner, Left, etc.)
    1. Expand the merged column

That’s how joins are actually done.

Power BI Relationships

This is where most beginners get confused.

Joins combine tables into one.
Relationships keep tables separate but connected.

Types of Relationships

1. One-to-Many (1:M)

Most common.

Example:

  • Customers (1)
  • Orders (Many)

One customer → many orders

2. Many-to-Many (M:M)

Both sides have duplicates.

Example:

  • Students ↔ Courses

Needs careful handling.

3. One-to-One (1:1)

Rare.

Used when splitting data for performance or organization.

Active vs Inactive Relationships

  • Active → used by default
  • Inactive → only used when explicitly called (via DAX like USERELATIONSHIP)

Example:
Order Date vs Delivery Date
Only one can be active at a time

Cardinality

Defines how tables relate:

  • 1:1
  • 1:M
  • M:M

Cross Filter Direction

Controls how filters move between tables.

  • Single direction → safer, recommended
  • Both directions → more flexible, but risky if misused

Where to Create Relationships in Power BI

Method 1: Model View

  1. Go to Model View (diagram icon)
  2. Drag one column onto another
  3. Set:
  • Cardinality
  • Cross-filter direction
  • Active/inactive

Method 2: Manage Relationships

  1. Go to Home → Manage Relationships
  2. Click New
  3. Select tables and columns
  4. Configure settings

Joins vs Relationships (Critical Difference)

Joins Relationships
Done in Power Query Done in Model View
Combine tables Keep tables separate
Static Dynamic
Used for shaping data Used for analysis

Simple way to remember:

  • Joins = data preparation
  • Relationships = data analysis

Fact vs Dimension Tables

This is the backbone of good data models.

Fact Tables

Contain measurable data.

Examples:

  • Sales amount
  • Quantity sold
  • Revenue

Dimension Tables

Contain descriptive data.

Examples:

  • Customer name
  • Product category
  • Date

Example

Fact Table: Orders

  • Order ID
  • Product ID
  • Customer ID
  • Revenue

Dimension Tables:

  • Customers
  • Products
  • Dates

Data Modeling Schemas

1. Star Schema (Best Practice)

Central fact table connected to dimension tables.

Structure:
Fact → Dimensions (no dimension-to-dimension links)

Why it’s powerful:

  • Fast
  • Clean
  • Easy to understand

Use case:
Most business dashboards (sales, marketing, finance)

2. Snowflake Schema

Dimensions are normalized (split further).

Example:
Product → Category → Department

Pros:

  • Reduces redundancy

Cons:

  • More complex
  • Slower queries

3. Flat Table (DLAT)

Everything in one table.

Pros:

  • Simple

Cons:

  • Redundant data
  • Poor performance at scale

Use case:
Small datasets or quick prototypes

Role-Playing Dimensions

A single dimension used multiple times.

Example:
Date table used as:

  • Order Date
  • Delivery Date

In Power BI:

  • Duplicate the Date table
  • Create separate relationships

Common Data Modeling Mistakes

From experience, these will mess you up:

  • Using flat tables for large datasets
  • Too many many-to-many relationships
  • Bi-directional filters everywhere
  • Not using a proper Date table
  • Mixing joins and relationships incorrectly

Data modeling is not the flashy part of Power BI.

But it’s the part that determines whether your analysis is:

  • Accurate
  • Scalable
  • Trustworthy

Right now at LuxDev, I’m starting to see that this is the real shift from “using tools” to actually thinking like a data analyst

Top comments (0)