DEV Community

SAMWEL SHIHANDE
SAMWEL SHIHANDE

Posted on

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

#analytics #data #database #tutorial

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])
)
Enter fullscreen mode Exit fullscreen mode

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].

  1. The filter is applied to DimProduct, keeping only laptop products.
  2. It travels along the relationship to FactSales, keeping only sales rows for those products.
  3. 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.

Top comments (0)