DEV Community: Joe

Data Modelling, Relationships And Joins In Power BI

Joe — Sun, 13 Sep 2026 12:49:16 +0000

Introduction

I used to think Power BI was mainly about dragging and dropping charts. If the data loaded successfully, I assumed the report would work.

Then I built a dashboard that took 45 seconds to refresh, and my DAX formulas started returning unexpected results.

That’s when I realized something important:

The problem wasn’t the charts. It was the model.

Data modelling in Power BI is about far more than simply “organizing tables.” The data model determines how filters flow through a report, how efficiently the model performs, and whether your calculations return reliable results.

In this article, I’ll walk through what I learned while fixing my own data models, including schemas, fact and dimension tables, relationships, filter direction, and the often-confusing difference between Power Query joins and Power BI model relationships.

Data Modelling in Power BI

At its core, data modelling is about deciding how your tables should interact.

I was working with a retail dataset containing customers, products, and sales. My first instinct was to load everything into one large table.

The Problem

It quickly became a mess.

“Alice” appeared thousands of times. “Nairobi” appeared thousands of times. The file became unnecessarily large, and writing DAX became more difficult because I had to work with repeated customer and location information just to calculate sales.

The Solution

I learned that a well-designed model separates context—who, what, and where—from events—how much or how many.

That reduces redundancy, simplifies DAX, improves performance, and makes the model easier to scale.

Flat Table

My First Attempt: The “Excel” Approach

I initially combined everything into one wide table:

For example:

Customer ID | Date       | Customer | Product | Category    | Location | Quantity | Sales
1001        | 01/09/2026 | Alice    | Laptop  | Electronics | Nairobi  | 1        | 80,000
1002        | 01/09/2026 | John     | Phone   | Electronics | Kisumu   | 2        | 60,000

The Verdict

It was easy to import, but difficult to maintain.

If Alice moved from Nairobi to Mombasa, for example, I could potentially need to update thousands of rows containing her information.

A flat table can work well for small and simple datasets, but as the model grows, duplication quickly becomes a problem.

Star Schema

The Fix

I switched to a Star Schema.

Instead of keeping everything in one table, I placed a Fact Table containing the measurable events at the centre and surrounded it with Dimension Tables containing the descriptive context.

For example:

Fact Sales: Customer Key, Product Key, Sales Amount
Dim Customer: Customer Key, Name, City
Dim Product: Product Key, Name, Category

Why It Worked

The structure was easier to understand, easier to maintain, and better suited to analysis in Power BI.

Instead of repeatedly storing customer and product information alongside every transaction, I could store that descriptive information once and connect it to the sales data through relationships.

The result was a cleaner model and simpler DAX.

Snowflake Schema

The Over-Engineering Trap

At one point, I tried to be “too proper” by further splitting my dimension tables.

For example, instead of keeping the product category within the product dimension, I created a separate DimCategory table that connected to DimProduct, which then connected to FactSales.

The structure worked, but it introduced additional relationships and made the model more complicated.

For my use case, the Star Schema was simpler and easier to work with.

The lesson I took away was simple: don’t introduce additional layers of normalization unless there is a clear reason to do so.

Fact Tables and Dimension Tables

Once I separated my tables, I needed to understand the role each type of table played.

Fact Tables

Fact tables contain the events or transactions being analysed.

They typically contain measurable values such as Sales and Quantity, along with the foreign keys needed to connect those transactions to dimension tables.

They answer questions such as:

How much?
How many?
How often?

For example, FactSales might contain:

Customer Key | Product Key | Date Key | Quantity | Sales Amount

Dimension Tables

Dimension tables provide the descriptive context used to analyse the facts.

They typically contain attributes such as names, categories, locations, and dates.

They answer questions such as:

Who?
What?
Where?
When?

For example, DimCustomer might contain:

Customer Key | Customer Name | City

This separation between facts and dimensions is one of the foundations of a good Power BI model.

Grain or Granularity of a Fact Table

This was one of the concepts I found most important to understand.

Grain means defining exactly what one row in a fact table represents.

In my FactSales table, for example, I might define the grain as:

One row = one product sold in one order.

That definition matters.

If I start mixing rows representing “one product sold” with rows representing “one entire order,” my calculations can become inconsistent and potentially produce incorrect results.

The key lesson is simple:

Define the grain clearly and keep it consistent.

Relationships in Power BI

Once I had separated my tables, I needed a way for them to work together.

That’s where relationships came in.

One-to-One

One-to-one relationships are relatively uncommon in typical Power BI models.

They can be useful in specific situations, such as when a table is intentionally split into two related tables.

One-to-Many: The Standard Relationship

This is the relationship pattern I use most often.

For example:

Dim Customer (1) connects to Fact Sales (*)
One customer can have many sales transactions.
The “one” side—the dimension—must contain unique keys.

This relationship allows a selection such as a customer, city, or other dimension attribute to filter the corresponding sales records.

Many-to-Many

This is where things can become more complicated.

I initially tried connecting FactSales directly to DimCategory, but the relationship did not behave the way I expected because the underlying data could involve multiple category associations.

The solution was to introduce a Bridge Table.

Instead of creating one direct many-to-many relationship, the bridge table allows the model to use two one-to-many relationships.

This provides a clearer and more controlled relationship structure.

Referential Integrity

I also learned the importance of referential integrity.

For example, if FactSales contains a CustomerID that does not exist in DimCustomer, Power BI may return a (Blank) member when the relationship is used.

That is a modelling issue, but it is also a data-quality issue.

A good model therefore depends not only on how tables are structured, but also on the quality and consistency of the underlying keys.

Active and Inactive Relationships

Another concept I had to understand was active versus inactive relationships.

My FactSales table contained both OrderDate and ShipDate, while my model had a DimDate table.

I could not simply make both relationships active at the same time in the way I wanted.

Instead, I kept OrderDate as the active relationship and used DAX when I needed to analyse sales based on ShipDate.

This allowed the same date dimension to support different date-based analyses without creating unnecessary complexity in the model.

Filter Direction

This was another area that explained why some of my slicers worked as expected while others did not.

Single Direction

With a single-direction relationship, filters generally flow from the Dimension → Fact.

For a standard Star Schema, this is usually the safest and simplest approach.

For example:

Dim Customer → Fact Sales

Selecting a customer filters the relevant sales records.

Bidirectional

With bidirectional filtering, filters can flow in both directions between related tables.

Although this can be useful in specific scenarios, I found that relying on it unnecessarily could introduce ambiguous filter paths and make the model harder to understand and troubleshoot.

It could also affect performance.

My Rule

Use single-direction filtering by default, and introduce bidirectional filtering only when there is a clear modelling requirement for it.

Joins in Power Query

Sometimes I needed to change or combine data before it entered the Power BI data model.

That’s where Power Query Merges became useful.

A merge allows me to combine queries based on matching columns.

Join Type	Records Retained	Example
Left Outer	All records from the left table plus matching records from the right	Find all customers, including those without orders
Right Outer	All records from the right table plus matching records from the left	Find all orders, including those without matching customers
Full Outer	All records from both tables	Identify matched and unmatched records from both sides
Inner	Only matching records	Find customers who placed orders
Left Anti	Records in the left table with no match in the right	Find customers who never placed an order
Right Anti	Records in the right table with no match in the left	Find orders without a matching customer

These joins are particularly useful when I need to transform or enrich the data before it is loaded into the model.

Power Query Joins vs. Power BI Relationships

This was probably one of the most confusing distinctions for me to understand.

A Power Query Merge and a Power BI relationship both connect data, but they serve different purposes.

Power Query Merge

A merge combines data during the data preparation stage.

For example, I might merge a customer table with another source to bring additional attributes into the customer query.

The resulting columns become part of the transformed data.

Power BI Relationship

A relationship connects tables within the data model.

Instead of physically combining the tables, the relationship allows Power BI's model engine to understand how the tables are connected and how filters should propagate between them.

My Rule

The distinction that helped me was:

Merge for enrichment. Relate for analysis.

If I need to bring additional columns into a table as part of data preparation, a Power Query Merge may be appropriate.

If I simply need separate tables to work together during analysis, a model relationship is usually the better approach.

My Recommended Power BI Model

After working through the different approaches—and making a few mistakes along the way—this is the architecture I generally recommend:

Schema: Star Schema
Relationships: One-to-Many (1:*) from Dimensions to Facts
Filter Direction: Single Direction

Why?

Because the model is:

Easier to understand
Easier to maintain
More predictable when writing DAX
Well suited to analytical reporting
Less likely to develop unnecessary relationship complexity

For example:

Dim Product  1 → *  Fact Sales
Dim Date     1 → *  Fact Sales
Dim Customer 1 → *  Fact Sales

With this structure, a straightforward measure such as:

SUM(FactSales[Sales Amount])

can work naturally with filters coming from the related dimension tables.

Conclusion

Data modelling isn’t just about making a Power BI report work.

It’s about building a model that remains accurate, understandable, and scalable as the data and reporting requirements grow.

I started with a flat table because it seemed simple. I then experimented with more complex structures before understanding why the Star Schema is so widely used for analytical models.

The biggest lesson I took away is this:

A good Power BI report starts with a good data model.

When the model is structured properly, relationships become easier to manage, DAX becomes simpler, filters behave more predictably, and the entire report becomes easier to maintain.

The charts are what users see.

The model is what makes them work.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

Joe — Sun, 23 Aug 2026 02:58:05 +0000

Working through a practical reference to moving a change through Git took me through the process of taking a single change from my local computer, committing it in Git, and pushing it to GitHub where others would be able to see it. This was written for complete Git Novices and so I approached it without any prior experience of using Git.

By the end, you will be able to move a change through all four Git stages and confirm it's visible on GitHub with a clear commit history.

Who This Is For

Anyone that want to work with Git in the terminal
Users who find git add, git commit, and git push unclear
No prior Git experience required

Prerequisites

Git installed (git --version to check)
A terminal or command-line application
A GitHub account (create one if needed)

We'll use a small sales data project as a running example. You do not need Python or Pandas installeda as we will be only tracking files, not running code.

The Four-Stage Flow

Every change travels in one direction:

Project Directory → Staging Area → Local Git Repository → Remote Github Repository

Project directory: where everything is worked on
Staging area: choosing what goes in
Local Git Repository(Commit): your historical record for everything worked on
Remote Github Repository(Push): share it so collaborators can see it

Setup: Initialize a Repository

Create a project folder and make it a Git repository:

mkdir monthly-sales
cd monthly-sales
git init

Expected output:

Initialized empty Git repository in /path/to/monthly-sales/.git/

git init creates a hidden .git folder where Git stores the entire history. From now on, Git watches this folder.

Usage

1. Working Directory (Untracked Files)

Create a raw data file:

echo "date,region,revenue" > sales_data.csv
echo "2026-01-01,East,1200" >> sales_data.csv

Check its status:

git status

Expected output:

On branch main
No commits yet
Untracked files:
  (use "git add <file>..." to include in what will be committed)
        sales_data.csv

Untracked means Git can see the file but isn't following it yet. This is the default for new files.

2. Staging Area (Choose What to Commit)

Stage the file:

git add sales_data.csv
git status

Expected output:

On branch main
No commits yet
Changes to be committed:
  (use "git rm --cached <file>..." to unstage)
        new file:   sales_data.csv

The label changes from Untracked files to Changes to be committed. The file is staged but not yet saved to history.

PRO TIP

If you edit two unrelated files (e.g., raw data and a cleaning script), you don't have to commit them together. Stage and commit one, then come back for the other. This keeps your history readable.

NOTE

git add . stages everything at once.
Always run git status first so you know exactly what that includes.

3. Commit (Local Snapshot)

Create a commit:

git commit -m "Add raw January sales data"

View the history:

git log

Expected output:

commit 6f3a1e2c9d8b4a17f0e3c5d9a2b1e8f7c3d4a5b6
Author: Some Rando <somerando@example.com>
Date:   Sat Aug 22 10:15:03 2026 +0000

    Add raw January sales data

A commit is a permanent snapshot of everything staged, saved with a message, timestamp, and author details. It's local until you push.Commit messages have two readers: your future self and collaborators.

PRO TIP

Writing Good Commit Messages:

Use the imperative mood ("Add", "Fix", "Update")

Say specifically what changed

Explain why when it isn't obvious

Cover one logical change

Weak	Strong
update	Add February revenue row to raw sales data
fix	Fix currency parsing for negative revenue values
changes	Drop rows with missing region before export
wipe	Add first draft of sales cleaning script

For longer commit messages, run git commit without -m to open your editor. Use a short summary line, a blank line, then a detailed body:

Fix currency parsing for negative revenue values

Regions reporting refunds submit revenue as "-120.00", which the
previous parser rejected as invalid. This adds a check for a
leading minus sign before the numeric conversion.

Demonstration: Separate Commits for Separate Changes

Make two changes—one to an existing file and one new file:

echo "2026-01-02,West,950" >> sales_data.csv
echo "import pandas as pd" > clean_sales.py
echo "df = pd.read_csv('sales_data.csv')" >> clean_sales.py
echo "df.to_csv('clean_sales.csv', index=False)" >> clean_sales.py

Check status:

git status

Expected output:

Changes not staged for commit:
        modified:   sales_data.csv

Untracked files:
        clean_sales.py

Stage and commit them separately:

git add sales_data.csv
git commit -m "Add February revenue row to raw sales data"

git add clean_sales.py
git commit -m "Add script to clean and export sales data"

git log

git log now shows three commits, each describing one specific change.

4. Push (GitHub)

Connect your local repository to an empty GitHub repository:

git remote add origin https://github.com/your-username/monthly-sales.git

Push:

git push -u origin main

Expected output:

Enumerating objects: 9, done.
Writing objects: 100% (9/9), done.
To https://github.com/your-username/monthly-sales.git
 * [new branch]      main -> main
branch 'main' set up to track 'origin/main'.

origin is the nickname for your GitHub URL.
main is the branch being pushed.
-u links your local main to origin/main, so future pushes can just be git push.

Verify on GitHub:

sales_data.csv and clean_sales.py are present
The commit history shows three separate, clearly labeled commits
Locally, git status reports nothing to commit (working tree clean)

That's the success signal: a local change, visible on GitHub, with a history that explains itself.

Troubleshooting

Symptom	Cause and Fix
`git commit` runs, but GitHub shows nothing	Commits are local only. Run `git push` to upload.
`nothing to commit, working tree clean`	Changes must be staged. Run `git add <file>`, then `git status`.
`git add .` staged files you didn't want	Run `git status` first. Use a `.gitignore` to exclude secrets, `.env`, etc.
Secrets committed by mistake	Treat them as compromised and rotate. Removing them later doesn't erase history.
`fatal: No configured push destination`	No remote is set. Run `git remote add origin <url>` before pushing.
Vague commit messages ("update", "fix stuff")	Not an error, but unreadable history. Follow the commit message guidelines.

Quick Reference

Stage	Command	Result
Working Directory	create/edit a file	Untracked or modified
Staging Area	`git add <file>`	"Changes to be committed"
Local Repository	`git commit -m "msg"`	Permanent local snapshot
Remote Repository	`git push`	Visible on GitHub

When in doubt, always run:

git status

Try It Yourself

In a new, empty folder:

Repeat all four stages with a data file or script of your own.
Edit two unrelated files in the same session.
Stage and commit them as two separate commits, each with a specific, imperative-mood message.
Run git log and confirm each commit describes exactly one change clearly enough that a teammate wouldn't need to ask what it means.