Data Modelling, Relationships and Joins in Power BI: A Practical Guide

SAMWEL SHIHANDE — Sun, 20 Sep 2026 08:58:43 +0000

A Power BI report is only as good as the model underneath it. I have seen beautiful dashboards give wrong totals, and simple-looking reports crawl for minutes, and the cause is almost always the same: a poorly designed data model. In this article I walk through how data modelling works in Power BI, how to compare flat, star and snowflake schemas, how relationships and filter direction behave, how joins work in Power Query, and how I would design a model for a real business intelligence project.

1. Data Modelling in Power BI

What is data modelling?

Data modelling in Power BI is the process of deciding which tables you need, what each table contains, and how those tables relate to each other. The model lives in the Model view of Power BI Desktop. It is the layer between your raw data sources and your visuals.

Power BI's engine (VertiPaq) stores data in memory in a compressed, column-based format. DAX calculations work by applying filter context that travels across relationships between tables. So the way you structure tables and relationships directly decides how fast reports run and whether the numbers are right.

Why a well-designed model matters

Reporting and analytics: Slicers, matrices and charts behave predictably when filters flow cleanly from descriptive tables to numeric tables.
DAX calculations: Measures are short and readable when the model is clean. A messy model forces long, defensive DAX with lots of FILTER, ALL and CROSSFILTER.
Performance: Smaller, narrower tables with fewer repeated text values compress better and scan faster.
Scalability: When new data sources, new metrics or new years arrive, a good model absorbs them without a rebuild.
Maintainability: A change such as renaming a product category happens in one place, not in millions of rows.

The three common approaches

1.1 Flat table

Definition: Everything is stored in one wide table. Sales figures, customer details, product details and dates all sit on the same row.

Structure: One table, many columns. Each row repeats all descriptive information.

erDiagram
    FlatSales {
        int OrderID
        date OrderDate
        string CustomerName
        string CustomerCity
        string ProductName
        string Category
        string StoreName
        int Quantity
        decimal SalesAmount
    }

Advantages

Very quick to build, with no relationships to manage.
Easy to understand for small datasets and one-off analysis.
Works well when the source is already a single CSV or Excel export.

Disadvantages

Heavy redundancy. "Nairobi" or "Electronics" is stored again on every row.
Larger file size and slower refresh as data grows.
Difficult to add a second business process (for example budgets) without duplicating dimension data.
Updates to descriptive data are hard to manage.
DAX for things like distinct customers or time intelligence gets awkward without a proper date table.

When it is appropriate: Small, one-off analyses, quick prototypes, or a dataset that will never grow or be reused.

Performance and complexity: Model complexity is low, but performance and maintainability get worse as the data grows, because repeated text values compress poorly and there are no shared dimensions.

1.2 Star schema

Definition: A star schema has one central fact table surrounded by dimension tables. When drawn, it looks like a star.

Structure: The fact table holds numeric business events and foreign keys. Each dimension holds descriptive attributes and has a unique key. Dimensions connect only to the fact table, never to each other.

erDiagram
    DimCustomer ||--o{ FactSales : "CustomerID"
    DimProduct ||--o{ FactSales : "ProductID"
    DimDate ||--o{ FactSales : "DateKey"
    DimLocation ||--o{ FactSales : "LocationID"

    FactSales {
        int SalesID PK
        int CustomerID FK
        int ProductID FK
        int DateKey FK
        int LocationID FK
        int Quantity
        decimal SalesAmount
    }
    DimCustomer {
        int CustomerID PK
        string CustomerName
        string Segment
    }
    DimProduct {
        int ProductID PK
        string ProductName
        string Category
        string Brand
    }
    DimDate {
        int DateKey PK
        date Date
        int Year
        string MonthName
    }
    DimLocation {
        int LocationID PK
        string City
        string Region
        string Country
    }

Screenshot placeholder: Insert your own Power BI Model view screenshot of the star schema here, with table names and the 1 and * markers on each relationship line visible.

Advantages

Simple, predictable filter flow: dimension to fact.
Fast queries, because the engine only needs one hop from dimension to fact.
Short, readable DAX.
Easy for report builders to understand, since dimensions are clearly labelled.
Works well with time intelligence through a dedicated date table.

Disadvantages

Some redundancy inside dimensions (for example a Category repeated on each product).
Requires upfront design and data preparation in Power Query or the source.

When it is appropriate: Almost all business intelligence models: sales, finance, HR, operations, marketing.

Performance and complexity: Excellent performance. VertiPaq compresses dimension columns well, and the fact table stays narrow because it holds mostly keys and numbers. Complexity is low to moderate.

1.3 Snowflake schema

Definition: A snowflake schema is a star schema where dimensions are normalised into further related tables. A dimension connects to other dimensions rather than only to the fact table.

Structure: For example, DimProduct links to DimProductSubcategory, which links to DimProductCategory. Similarly DimLocation links to DimRegion, then to DimCountry.

erDiagram
    DimProductCategory ||--o{ DimProductSubcategory : "CategoryID"
    DimProductSubcategory ||--o{ DimProduct : "SubcategoryID"
    DimProduct ||--o{ FactSales : "ProductID"
    DimCountry ||--o{ DimRegion : "CountryID"
    DimRegion ||--o{ DimLocation : "RegionID"
    DimLocation ||--o{ FactSales : "LocationID"
    DimDate ||--o{ FactSales : "DateKey"

    FactSales {
        int SalesID PK
        int ProductID FK
        int LocationID FK
        int DateKey FK
        int Quantity
        decimal SalesAmount
    }

Advantages

Less redundancy in dimensions.
Mirrors how many transactional databases are already structured.
Changes to a higher level (for example a category name) happen in one table.

Disadvantages

More tables and more relationships to maintain.
Filters must travel through several hops, which adds complexity and can slow queries.
Harder for report authors to navigate, since a single business concept is split across many tables.
Usually saves very little space in Power BI, because dimensions are small compared with facts.

When it is appropriate: When a dimension is very large and a sub-dimension is shared by several other tables, or when you are modelling directly on top of a normalised source and cannot reshape it. Even then, I usually flatten the dimension in Power Query.

Performance and complexity: Slightly slower and noticeably more complex than a star schema.

Quick comparison

Feature	Flat table	Star schema	Snowflake schema
Number of tables	1	1 fact + several dimensions	1 fact + dimensions + sub-dimensions
Redundancy	High	Low to moderate	Lowest
Query speed	Slows as data grows	Fast	Slightly slower
DAX simplicity	Moderate	High	Moderate
Ease of use	Easy at first	Easy	Harder
Best for	Small, one-off analysis	Most BI projects	Normalised sources, shared sub-dimensions

2. Fact Tables and Dimension Tables

Fact tables

A fact table records business events or transactions. It usually stores:

Numeric measures you want to aggregate: SalesAmount, Quantity, DiscountAmount, Cost.
Foreign keys pointing to dimension tables: CustomerID, ProductID, DateKey, LocationID.
Sometimes a transaction identifier such as OrderID or InvoiceNumber.

Fact tables are typically long (many rows) and narrow (few columns). Common examples are FactSales, FactOrders and FactTransactions.

Dimension tables

A dimension table stores descriptive attributes, the "who, what, where, when" used to slice and filter the facts. Each dimension has a unique key.

DimCustomer: CustomerID, name, segment, join date.
DimProduct: ProductID, product name, category, brand, unit price.
DimDate: date, year, quarter, month, weekday, fiscal period.
DimLocation: LocationID, city, region, country.

Dimensions are typically short (fewer rows) and wide (many descriptive columns).

Measures versus attributes

Numeric business events (facts)	Descriptive attributes (dimensions)
SalesAmount, Quantity, Cost	Customer name, product category, city
Added up, averaged, counted	Used for grouping, filtering and labels
Changes with every transaction	Changes rarely

Grain (granularity)

The grain is what one row of the fact table represents. It is the most important decision in the design, and you should state it in a single sentence.

For example: "One row in FactSales represents one product sold on one order line."

If the grain is one line per order line, you can answer questions by product, customer, day or store. If the grain were one row per month per store, you could no longer report by product or by day. A rule I follow: keep the grain as detailed as the business will ever need, and never mix different grains in the same fact table.

A practical example

Imagine a retail company. Every time a product is sold, a row is added to FactSales.

FactSales

SalesID	DateKey	CustomerID	ProductID	LocationID	Quantity	SalesAmount
1	20260901	101	5	1	2	5,000
2	20260901	102	7	2	1	7,200
3	20260902	101	7	1	3	21,600

DimCustomer

CustomerID	CustomerName	Segment
101	Amina	Retail
102	Brian	Corporate

The fact table is connected to DimCustomer, DimProduct, DimDate and DimLocation, exactly as in the star schema diagram in section 1.2. A user can now pick "Corporate" in a slicer, and the total in a card visual changes to include only Brian's sales, without ever storing the segment in FactSales.

3. Relationships in Power BI

What is a relationship?

A relationship links two tables through a shared column, so that filters applied to one table affect the other. Relationships are necessary because, in a good model, data is split across multiple tables. Without relationships, selecting "Electronics" in a product slicer would do nothing to the sales table, because Power BI would not know how the two are connected.

Importantly, a relationship does not merge the tables or copy any data. It only tells the engine how to propagate filters.

Key concepts

Primary key: A column that uniquely identifies each row in a table (for example CustomerID in DimCustomer).
Foreign key: A column in another table that refers to a primary key (for example CustomerID in FactSales).
Unique values: The "one" side of a relationship must contain unique, non-blank values.
Cardinality: Describes how many matching rows exist on each side of a relationship.
Referential integrity: Every foreign key value in the fact table should have a matching key in the dimension. When it does not, Power BI shows a (Blank) group in visuals for those unmatched rows.
Active and inactive relationships: Only one relationship between two tables can be active at a time. Inactive relationships appear as dashed lines and are used only when a DAX measure calls USERELATIONSHIP.

Why CustomerID is unique in one table and repeated in another: In DimCustomer, CustomerID appears once because each customer is a single record. In FactSales, the same CustomerID appears as many times as that customer made purchases. Customer 101 is one row in the dimension but might appear in fifty sales rows. That is the classic one-to-many relationship.

3.1 One-to-many (1:*)

How it works: One row in the "one" table matches many rows in the "many" table. The one side must have unique values.

Example: DimProduct (one) to FactSales (many). One product can appear on thousands of sales rows.

erDiagram
    DimProduct ||--o{ FactSales : "1 to many"

When to use: This is the default and the workhorse of Power BI models. Use it between every dimension and its fact table.

When not to use: It cannot be used when the "one" column contains duplicates. Fix the data or reconsider the design.

3.2 One-to-one (1:1)

How it works: Each row in one table matches at most one row in the other, and both columns are unique.

Example: DimCustomer and DimCustomerProfile, where the profile table stores extra details such as birthday and preferences.

erDiagram
    DimCustomer ||--|| DimCustomerProfile : "1 to 1"

When to use: Rarely. It can be used when one table is split for security or source reasons.

When not to use: In most cases, a one-to-one relationship suggests the two tables should be merged into one in Power Query, which simplifies the model.

3.3 Many-to-many (:)

How it works: Neither column has unique values, so many rows on each side can match. Power BI lets you create this cardinality directly, but results can be ambiguous.

Example: A SalesTargets table with one target per region per month, related to FactSales on Region. Region repeats on both sides.

erDiagram
    FactSales }o--o{ SalesTargets : "Region (many to many)"

When to use: Only when a bridge table is not practical, or when relating two fact tables at different grains as a last resort.

When not to use: As a habit. The better fix is almost always to add a shared dimension (here, DimRegion) and relate both tables to it with one-to-many relationships, which gives a proper star schema.

Screenshot placeholder: Insert your own Model view screenshot showing the three cardinalities with the 1, * and relationship lines visible.

Active and inactive relationships

A common case is role-playing dates. FactSales has both OrderDate and ShipDate, and both link to DimDate. Only one can be active (say OrderDate). The other stays inactive and is used in a measure:

Sales by Ship Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(FactSales[ShipDate], DimDate[Date])
)

4. Filter Direction

How filters propagate

Filter direction defines which way filters travel across a relationship. In a well-designed model, filters flow from the "one" side to the "many" side, in other words from dimensions to facts.

Single-direction filtering

With single direction, a filter on the dimension affects the fact table, but a filter on the fact table does not affect the dimension.

Example: A user selects "Laptops" in a slicer built from DimProduct[Category].

The filter is applied to DimProduct, keeping only laptop products.
It travels along the relationship to FactSales, keeping only sales rows for those products.
Any visual that sums FactSales[SalesAmount] now shows laptop sales only.

flowchart LR
    A["DimProduct<br/>Category = Laptops"] -->|filter flows| B["FactSales<br/>only laptop rows"]

Both (bidirectional) filtering

With Both, filters travel in both directions. A selection on the fact table can also filter the dimension.

Where it can help: Filtering a slicer so it only shows dimension values that actually have sales, or handling certain many-to-many scenarios.

Why bidirectional filtering needs care

Ambiguous filter paths: If two dimensions are connected to each other through the fact table and both are bidirectional, Power BI may not know which path to use, and results can be wrong or the relationship can be blocked.
Unexpected results: Filters spread through the model in ways report users do not expect.
Performance: More filter propagation means more work for the engine.
Unnecessary complexity: Models become harder to reason about and debug.

My rule is to keep everything single-direction by default. When I need a filter to work in the reverse direction, I try a DAX measure first (for example using CROSSFILTER inside CALCULATE), and only turn on Both for a specific, well-understood reason.

5. Joins in Power Query

What is a join?

A join combines rows from two tables based on a matching column. In Power Query, this is done with Home > Merge Queries (or Merge Queries as New). You choose two tables, select the matching column in each, and pick a join kind. Power Query then adds a column of nested tables, which you expand to bring in the columns you need.

Screenshot placeholder: Insert your own screenshot of the Merge dialog, showing both tables, the matching columns and the Join Kind dropdown.

Example data

Customers

CustomerID	Name
C1	Amina
C2	Brian
C3	Chloe
C4	David

Orders

OrderID	CustomerID	Amount
O101	C1	5,000
O102	C1	2,500
O103	C2	7,200
O104	C5	1,800

Customers is the first (left) table and Orders is the second (right) table. Note that Chloe and David have no orders, and order O104 belongs to a customer (C5) who does not exist in Customers.

5.1 Left Outer Join

How it works: Keeps all rows from the left table and only the matching rows from the right.

Records retained: All customers, with order details where they exist. Unmatched customers get null values.

CustomerID	Name	OrderID	Amount
C1	Amina	O101	5,000
C1	Amina	O102	2,500
C2	Brian	O103	7,200
C3	Chloe	null	null
C4	David	null	null

Use it for: Listing every customer and their orders, including customers who have not bought anything. It is the most common join.

5.2 Right Outer Join

How it works: Keeps all rows from the right table and only matching rows from the left.

Records retained: All orders, with customer details where they exist.

CustomerID	Name	OrderID	Amount
C1	Amina	O101	5,000
C1	Amina	O102	2,500
C2	Brian	O103	7,200
C5	null	O104	1,800

Use it for: Making sure no orders are lost, and spotting orders that have no matching customer record.

5.3 Full Outer Join

How it works: Keeps all rows from both tables, matching where possible and filling with nulls where not.

CustomerID	Name	OrderID	Amount
C1	Amina	O101	5,000
C1	Amina	O102	2,500
C2	Brian	O103	7,200
C3	Chloe	null	null
C4	David	null	null
C5	null	O104	1,800

Use it for: Reconciling two sources and seeing everything that matches and everything that does not.

5.4 Inner Join

How it works: Keeps only rows that match in both tables.

CustomerID	Name	OrderID	Amount
C1	Amina	O101	5,000
C1	Amina	O102	2,500
C2	Brian	O103	7,200

Use it for: Analysing only customers who have placed orders. Be careful, because it silently drops unmatched rows.

5.5 Left Anti Join

How it works: Keeps only rows from the left table that have no match in the right.

CustomerID	Name
C3	Chloe
C4	David

Use it for: Finding customers who never ordered, products that never sold, or employees with no assigned department.

5.6 Right Anti Join

How it works: Keeps only rows from the right table that have no match in the left.

OrderID	CustomerID	Amount
O104	C5	1,800

Use it for: Finding orphan records, such as orders whose customer is missing. It is a very useful data quality check before creating relationships.

Summary

Join kind	Rows kept	Result rows (this example)
Left Outer	All left + matching right	5
Right Outer	All right + matching left	4
Full Outer	All rows from both	6
Inner	Only matching rows	3
Left Anti	Left rows with no match	2
Right Anti	Right rows with no match	1

6. Power Query Joins vs Power BI Relationships

Both connect tables, but they are very different operations.

	Power Query merge (join)	Model relationship
What it does	Physically combines columns and rows into a result table	Links tables logically, without combining them
When it happens	During data loading and transformation, at each refresh	In the data model, at query time when a visual is used
Result	A new or wider table	Tables stay separate
Data duplicated?	Yes, values are repeated on each row	No
Flexibility	Fixed once loaded	Filters are dynamic and respond to slicers

Does a merge physically combine data? Yes. The columns from the second table are added into the result, and the merged data is stored as one table when it loads.

Does creating a relationship combine the tables? No. The tables remain separate. Power BI just uses the relationship to pass filters between them.

At what stage does each occur? Merges happen in Power Query, the transformation stage, before data is loaded. Relationships are created in the model stage, after loading, and are evaluated while reports run.

When would I choose a merge?

To flatten a snowflaked dimension into one dimension table (for example, joining product, subcategory and category).
To bring a lookup column into a table when it is truly one-to-one.
To do data quality checks with anti joins.
To clean and reshape data before loading it.

How can excessive merging hurt a model? Merging everything into one giant table recreates the flat table problem: heavy redundancy, bigger file size, slower refresh, harder maintenance, and difficulty adding new data sources. It also mixes grains, which leads to double counting.

Why keep facts and dimensions separate? Separate tables keep the fact table narrow and the descriptive data stored once. Filters stay clean, DAX stays simple, updates are made in one place, and the model can grow with new fact tables that share the same dimensions (for example FactSales and FactBudget both using DimDate).

Example: If I merge DimCustomer into FactSales, customer name and segment are copied onto every sales row, and a new customer attribute requires reloading the full fact table. If I keep them separate and use a relationship, DimCustomer is stored once and I can add an attribute there without touching the fact table.

7. Recommended Power BI Model

For a typical business intelligence project, I would use a star schema, with one-to-many relationships and single-direction filters from dimensions to facts.

Why a star schema

Performance: Narrow fact tables, small dimensions and one-hop filters compress well and scan quickly.
DAX simplicity: Measures such as SUM(FactSales[SalesAmount]) work with natural filter context, with no need for complex filter overrides.
Readability: Anyone opening the model can see what the facts are and how they can be sliced.
Scalability: New facts (budgets, returns) can be added and connected to the same shared dimensions.
Low redundancy: Descriptive data is stored once, in dimensions.
Maintainability: Changes happen in one place, and the structure is well known to other BI developers.
Ease of report building: Report authors pick fields from clearly named dimension tables.
Filter propagation: Filters follow one predictable path, dimension to fact.

Why not the alternatives

Flat table: Fine for small, one-off work, but it does not scale and it makes DAX and maintenance harder.
Snowflake: Adds hops and tables for little benefit in Power BI. If the source is snowflaked, I flatten the dimensions in Power Query.

Relationship design I would implement

Cardinality: One-to-many from each dimension to the fact table.
Filter direction: Single, from dimension to fact, by default.
Bidirectional filtering: Avoided unless there is a specific, tested reason.
Date table: A dedicated DimDate, marked as the date table, with an active relationship on the main date and inactive relationships for others, activated with USERELATIONSHIP.
Keys: Integer surrogate keys where possible, unique and non-blank on the dimension side.
Integrity checks: Anti joins in Power Query to find fact rows with no matching dimension row before loading.
Hidden fields: Foreign keys in the fact table are hidden from report view so authors use dimension fields.
Many-to-many: Avoided by introducing shared dimensions or bridge tables.

Conclusion

Good Power BI models are not complicated, they are deliberate. Use Power Query to clean, reshape and check the data, use a star schema to organise it, and use simple one-to-many relationships with single-direction filters to connect it. When something goes wrong in a report, start by checking the grain of your fact table, the uniqueness of your dimension keys, and the direction of your filters. Most modelling problems are found in one of those three places.

My First GitHub Project: From A Local Folder To GitHub Using Git And SSH

SAMWEL SHIHANDE — Sun, 23 Aug 2026 01:02:57 +0000

Introduction.

I have folder with files sitting quietly on my laptop, and the idea of turning it into a "real project" on github feels like a mountain. This week, i climbed that mountain and i want to walk you through exactly how i did.

Git vs GitHub.

Git is a tool that lives on my computer and its the thing that actually tracks the changes i make to my files
GitHub is a website. Its where i can store a copy of that diary online so i can share it with other people, back it up or work on it from a different computer. Git does the tracking and GitHub does the hosting.

Step 1: Creating the folder.

Using Git Bash, i first create a folder called my-first-project.
Git Bash is a command-line tool that allows users to interact with Git and perfom common computer tasks using commands.

Git Bash folder commands

ls -view the files and folders
mkdir -create a new folder
_cd _-Enter the folder
cd .. -Go back one folder

Step 1: Turning my local folder into a Git project

To make Git start paying attention to _my-first-project, i opened my terminal, navigate into the folder and run the commands above.

Step 2: Setting up SSH so GitHub actually trusts me.

This part is what made me feel like a real developer for the first time. Think of SSH key like a digital handshake. My computer has one half of the key and GitHub has the other half key and they recognize each other without me typing password every single time.
Here's how i set mine up:
Generate the key on my machine
I use the command-line ssh-keygen -T ed25519 -C "sshihande@gmail.com" on Git Bash.
This creates two files, a private key and a public key.
Copy the public key:
I use the command-line cat_which is used to copy the public key.
To print the public key in my terminal i use the run:_ cat ~/.ssh/id_ed25519.pub
Add it to GitHub:
I logged into GitHub and pasted it in.
Tested the connection:
I ran _ssh -T git@github.com_to test the connection. The first time it warned me about connecting to a new host and asked me if i wanted to continue, i typed yes

Step 3: creating the Repository on GitHub

On GitHub i clicked new repository, gave it the same name as my local folder and this part matters.

Step 4: Connecting my local folder to that repository

I think of origin as just a nickname Git uses to refer to that GitHub repository so i dont have totype the full url every time.

Step 5: The actual workflow. Staging, committing and pushing.

This is the part of Git that took me a while to really get, so let me break iTt down the way i finally understood it.
Working directory -This is just my folder as it exists right now, with all my raw changes, Git sees that something changed but hasn't been told to care about it yet.
Staging Area -This is like a waiting room. I choose exactly which changes i want to include in my next save point. I do this with Git Bash.
Commit -This is the actual save point in the projects history. I learnt that a commit message should actually describe what changed.
Push -This is where my local commit atually travels up to GitHub.

What i actually learnt from doing all this.

If you are about to do this for the first time dont rush past the ssh set-up.It was intimidating at first bur i ended up enjoying .

DEV Community: SAMWEL SHIHANDE

Data Modelling, Relationships and Joins in Power BI: A Practical Guide

1. Data Modelling in Power BI

What is data modelling?

Why a well-designed model matters

The three common approaches

1.1 Flat table

1.2 Star schema

1.3 Snowflake schema

Quick comparison

2. Fact Tables and Dimension Tables

Fact tables

Dimension tables

Measures versus attributes

Grain (granularity)

A practical example

3. Relationships in Power BI

What is a relationship?

Key concepts

3.1 One-to-many (1:*)

3.2 One-to-one (1:1)

3.3 Many-to-many (:)

Active and inactive relationships

4. Filter Direction

How filters propagate

Single-direction filtering

Both (bidirectional) filtering

Why bidirectional filtering needs care

5. Joins in Power Query

What is a join?

Example data

5.1 Left Outer Join

5.2 Right Outer Join

5.3 Full Outer Join

5.4 Inner Join

5.5 Left Anti Join

5.6 Right Anti Join

Summary

6. Power Query Joins vs Power BI Relationships

7. Recommended Power BI Model

Why a star schema

Why not the alternatives

Relationship design I would implement

Conclusion

Amazing

My First GitHub Project: From A Local Folder To GitHub Using Git And SSH

My First GitHub Project: From A Local Folder To GitHub Using Git And SSH

Introduction.

Git vs GitHub.

Step 1: Creating the folder.

Git Bash folder commands

Step 1: Turning my local folder into a Git project

Step 2: Setting up SSH so GitHub actually trusts me.

Step 3: creating the Repository on GitHub

Step 4: Connecting my local folder to that repository

Step 5: The actual workflow. Staging, committing and pushing.

What i actually learnt from doing all this.