Data Modelling, Relationships and Joins - PowerBI

Introduction

A Power BI report is only as good as the data model behind it. DAX calculations, visual interactions, refresh performance, and long-term maintainability all depend on how tables are structured, related, and filtered. This article explains data modelling concepts in Power BI, compares flat, star, and snowflake schemas, explains fact and dimension tables, covers relationship cardinalities and filter direction, demonstrates Power Query joins, and recommends a practical model design for real-world BI projects.

1. Data Modelling in Power BI

Data modelling in Power BI means designing the tables, columns, relationships, hierarchies, and measures that form the analytical layer of a report. It is not simply loading data and dragging fields onto a canvas. A good model decides:

• Which tables should exist.
• What grain each table represents.
• Which columns are keys, attributes, or measures.
• How tables relate to one another.
• How filters should flow.
• Which calculations belong in DAX versus Power Query.

A well-designed model matters because it directly affects:

• Reporting - Users can slice data intuitively and consistently.
• Analytics - Business questions are answered with correct aggregations.
• DAX - Measures are simpler and less error-prone.
• Performance - Fewer relationships, smaller tables, and efficient scans improve speed.
• Scalability - New facts and dimensions can be added without redesign. Maintainability Logic is centralised and easier to audit.

1.1 Flat Table

Definition

A flat table stores all data in one wide table. Each row contains both descriptive attributes and numeric measures.

Structure

All columns - customer name, product name, date, category, quantity, sales amount - live in the same table.

FlatSales

Advantages

• Simple to create and understand.
• Fast for very small datasets or quick prototypes.
• No relationship design required.

Disadvantages

• Heavy redundancy: customer and product details repeat on every row.
• Large file size and slower refresh as data grows.
• Difficult DAX: distinct counts and semi-additive measures become complex.
• Poor scalability and maintainability.
• Filtering is limited because there are no separate dimension tables.

When Appropriate

• Very small personal reports.
• One-off analysis where the data will not grow.
• Initial data exploration before building a proper model.

Power BI Implications

Power BI can query a flat table, but the model will not take advantage of columnar compression as effectively as a star schema. Report performance and DAX simplicity usually suffer.

1.2 Star Schema

Definition

A star schema consists of one or more central fact tables surrounded by dimension tables. It is the most common and recommended schema for Power BI.

Structure

The fact table stores business events and numeric measures. Dimension tables store descriptive attributes and connect to the fact table through keys.

Sales Schema Diagram

erDiagram
    DimDate ||--o{ FactSales : "1:*"
    DimCustomer ||--o{ FactSales : "1:*"
    DimProduct ||--o{ FactSales : "1:*"
    DimLocation ||--o{ FactSales : "1:*"

Example tables:

FactSales

DimCustomer

DimProduct

Advantages

• Excellent Power BI performance due to columnar compression and simple relationships.
• Simple DAX: measures aggregate the fact table and filters flow from dimensions.
• Intuitive for report authors and business users.
• Scalable: new facts and dimensions can be added with minimal disruption.
• Supports conformed dimensions shared across multiple fact tables.

Disadvantages

• Requires ETL work to separate facts and dimensions.
• Some redundancy remains in dimension tables, which is usually acceptable.
• Poorly designed dimensions can still cause performance issues.

When Appropriate

• Most business intelligence projects.
• Any model with multiple business questions, multiple facts, or growth expectations.
• Models using DAX heavily.

Power BI Implications

Power BI’s VertiPaq engine is optimised for star schemas. Relationships are simple, filter propagation is predictable, and DAX is easier to write and tune.

1.3 Snowflake Schema

Definition

A snowflake schema is a star schema where dimension tables are normalised into additional related tables.

Structure

Instead of one wide DimProduct, product category and department are stored in separate tables.

Product Hierarchy Snowflake Schema

erDiagram
    FactSales }o--|| DimProduct : "*:1"
    DimProduct }o--|| DimCategory : "*:1"
    DimCategory }o--|| DimDepartment : "*:1"

Example:

DimProduct

DimCategory

DimDepartment

Advantages

• Reduces redundancy in dimension data.
• Can simplify governance when dimensions are very large and shared.
• Useful when source systems are already normalised.

Disadvantages

• More tables and relationships increase model complexity.
• DAX may require more relationship navigation.
• Performance can degrade if too many small joins are introduced.
• Report authors may find it harder to locate fields.

When Appropriate

• Very large dimensions where normalisation saves significant space.
• Enterprise models with shared conformed dimensions.
• Source systems that already provide normalised dimension tables.

Power BI Implications

Power BI can handle snowflake schemas, but the star schema is generally preferred. Snowflake dimensions should be used selectively, and the reporting layer should still feel like a star.

2. Fact Tables and Dimension Tables

Fact Tables

Fact tables store numeric business events and foreign keys. They are usually tall and narrow.

Typical fact table columns:

• Foreign keys to dimensions: DateKey, CustomerID, ProductID, LocationID.

• Degenerate dimensions: OrderNumber, InvoiceNumber.

• Measures: Quantity, SalesAmount, CostAmount, DiscountAmount.

Examples:

• FactSales
• FactOrders
• FactTransactions
• FactInventory

Dimension Tables

Dimension tables store descriptive attributes used for slicing and grouping. They are usually shorter and wider.

Typical dimension table columns:

• Primary key: CustomerID, ProductID, DateKey.
• Descriptive attributes: CustomerName, City, Country, ProductName, Category, Year, Month.

Examples:

• DimCustomer
• DimProduct
• DimDate
• DimLocation
• DimEmployee

Measures vs Attributes

A measure is a numeric value that can be aggregated, such as SalesAmount. An attribute is a descriptive field used for filtering or grouping, such as ProductCategory or CustomerCity.

Grain of a Fact Table

The grain defines what one row in the fact table represents. For example:

FactSales grain: one row per product per sales order line.

If a row represents an entire order, then quantity and sales amount cannot be analysed by product. Grain must be defined before modelling because it determines which dimensions can relate to the fact table.

Practical Star Schema Example

A retail company wants to analyse sales by date, customer, product, and location.

Sales Star Schema Diagram

erDiagram
    DimCustomer ||--o{ FactSales : "connects to"
    DimProduct ||--o{ FactSales : "connects to"
    DimDate ||--o{ FactSales : "connects to"
    DimLocation ||--o{ FactSales : "connects to"

FactSales:

• DateKey
• CustomerID
• ProductID
• LocationID
• Quantity
• SalesAmount

DimDate:

• DateKey
• Date
• Year
• Quarter
• Month

DimCustomer:

• CustomerID
• CustomerName
• City
• Country

DimProduct:

• ProductID
• ProductName
• Category
• Subcategory

DimLocation:

• LocationID
• StoreName
• Region
• Country

This design allows a user to slice SalesAmount by Year, Customer City, Product Category, or Store Region with simple, fast DAX.

3. Relationships in Power BI

A relationship in Power BI connects two tables using common columns, usually a primary key in one table and a foreign key in another. Relationships are necessary because data is distributed across multiple tables. They allow filters to propagate and enable DAX to aggregate related data correctly.

Relationship Cardinalities

One-to-Many (1:*)

This is the most common relationship.

How it works:

• One row in the dimension table relates to many rows in the fact table.
• The dimension side contains unique values.
• The fact side can contain repeated values.

Example:

• DimCustomer[CustomerID] 1 → * FactSales[CustomerID]
• CustomerID is unique in DimCustomer but appears many times in FactSales.

Customer to Sales Relationship

erDiagram
    DimCustomer ||--o{ FactSales : "1:*"

    DimCustomer {
        int CustomerID PK
    }
    FactSales {
        int CustomerID FK
    }

When to use:

• Almost always between dimensions and facts.
• It is the foundation of a star schema.

When not to use:

• Avoid if the “one” side is not unique.
• Avoid if the relationship creates ambiguous filter paths.

One-to-One (1:1)

How it works:

• One row in each table relates to exactly one row in the other.
• Both sides contain unique values.

Example:

DimEmployee[EmployeeID] 1 ↔ 1 DimEmployeeDetails[EmployeeID]

Employee 1-to-1 Relationship

erDiagram
    DimEmployee ||--|| DimEmployeeDetails : "1:1"

When to use:

• Separating optional or sensitive attributes.
• Merging two tables with the same grain when a relationship is preferred over a merge.

When not to use:

• If the tables can be merged cleanly.
• If it adds complexity without analytical benefit.

Many-to-Many (:)

How it works:

• Many rows in one table relate to many rows in another.
• Usually implemented through a bridge table.

Example:

• Students and Courses. A student takes many courses, and a course has many students.

Student-Course Many-to-Many Relationship

erDiagram
    DimStudent ||--o{ BridgeEnrollment : "1:*"
    DimCourse ||--o{ BridgeEnrollment : "1:*"

When to use:

• True many-to-many business relationships.
• Bridge tables with measures or weighting factors.

When not to use:

• Avoid direct many-to-many relationships unless necessary.
• They can produce unexpected results and complicated DAX.

Key Concepts

• Primary Key - Unique identifier in a dimension table, e.g. DimCustomer[CustomerID].
• Foreign Key -Column in a fact table that references a dimension, e.g. FactSales[CustomerID].
• Unique Values - Required on the “one” side of a 1:* relationship.
• Cardinality - The number of rows that can relate: 1:*, 1:1, :.
• Referential Integrity - Every foreign key value should exist in the related primary key table. If not, Power BI may show blank rows.
• Active Relationship - The default relationship used for filter propagation and DAX.
• Inactive Relationship - A relationship that exists but is not active by default. Used with USERELATIONSHIP in DAX.

Example: CustomerID contains unique values in DimCustomer because each customer appears once. It appears multiple times in FactSales because one customer can place many orders. This is a classic 1:* relationship.

Active and inactive relationships are common with dates. FactSales may have OrderDateKey, ShipDateKey, and DeliveryDateKey. Only one can be active at a time. A measure can use USERELATIONSHIP to activate another date relationship for specific calculations.

4. Filter Direction

Filter direction determines how filters move between related tables.

Single-Direction Filtering

Filters flow from the “one” side to the “many” side.

Example:

• DimProduct[Category] filters FactSales.

If a user selects Category = Bikes, Power BI filters DimProduct to Bikes, and that filter propagates to FactSales, showing only bike sales.

Product to Sales Relationship (Single Filter Direction)

erDiagram
    DimProduct ||--o{ FactSales : "Filters ──>"

Single-direction filtering is the default and recommended approach for most star schemas.

Both / Bidirectional Filtering

Filters can flow in both directions.

Example:

• DimProduct filters FactSales, and FactSales can filter DimProduct.

Bidirectional filtering is sometimes used for:

• Many-to-many bridge tables.
• Dimension-to-dimension filtering.
• Certain DAX patterns.

However, it should be used carefully because it can cause:

• Ambiguous filter paths.
• Unexpected results.
• Slower performance.
• Circular relationship issues.
• Unnecessary model complexity.

Example of ambiguity: If DimCustomer and DimProduct both filter FactSales, and FactSales filters both back, Power BI may not know which path should influence the other. This can produce incorrect totals or require complex DAX overrides.

Best practice: Use single-direction filtering by default. Use bidirectional only when a specific analytical requirement cannot be met otherwise, and document it clearly.

5. Joins in Power Query

A join combines rows from two tables based on matching column values. In Power BI, joins are performed in Power Query using Merge Queries.

Example tables:

Customers

Orders

Left Outer Join

How it works:

• Returns all rows from the left table.
• Returns matching rows from the right table.
• Unmatched right columns become null.

Records retained:

• All Customers.
• Matching Orders.

Expected output:

Inner / Left Join Result

Order 105 is excluded because there is no matching customer.

Right Outer Join

How it works:

• Returns all rows from the right table.
• Returns matching rows from the left table.
• Unmatched left columns become null.

Records retained:

• All Orders.
• Matching Customers.

Expected output:

Right Join Result

Carol is excluded because she has no order.

Full Outer Join

How it works:

• Returns all rows from both tables.
• Matches where possible.
• Unmatched columns become null.

Records retained:

• All Customers and all Orders.

Expected output:

Full Outer Join Result

Inner Join

How it works:

• Returns only rows that match in both tables.

Records retained:

• Only matching Customers and Orders.

Expected output:

Inner Join Result

Carol and Order 105 are excluded.

Left Anti Join

How it works:

• Returns rows from the left table that have no match in the right table.

Records retained:

• Customers without Orders.

Expected output:

Unmatched Customers (Left Anti Join Result)

Right Anti Join

How it works:

• Returns rows from the right table that have no match in the left table.

Records retained:

• Orders without Customers.

Expected output:

Orphan Orders (Right Anti Join Result)

Power Query Illustration

1. Left Outer Join

Keeps all customers; unmatched customers get null order details.

2. Right Outer Join

Keeps all orders; orphan orders get null customer details.

3. Full Outer Join

Keeps everything from both tables, filling in nulls where there are no matches.

4. Inner Join

Only returns exact matches between both tables.

5. Left Anti Join

Isolates customers who have never placed an order.

6. Right Anti Join

Isolates orphan orders that don't match any customer.

In Power Query: Home → Merge Queries → select matching columns → choose join kind → expand the new table column.

6. Power Query Joins vs Power BI Relationships

A Power Query merge physically combines columns from tables. A Power BI relationship does not combine tables; it tells the model how tables are related.

You would choose a merge when:

• You need to add lookup columns during ETL.
• You need to create a bridge table.
• You need to flatten a small dimension for a specific report.
• You need to perform anti-join validation.

You would choose a relationship when:

• You want filter propagation.
• You want to keep fact and dimension tables separate.
• You want simple DAX and good performance.
• You want a reusable star schema.

Excessive merging can destroy a good model. It creates wide tables, duplicates data, increases refresh time, and makes DAX harder. Keeping fact and dimension tables separate is usually preferable because it supports a clean star schema, improves compression, and simplifies reporting.

7. Recommended Power BI Model

For a typical business intelligence project, I recommend a star schema with conformed dimensions and single-direction relationships.

Sales Star Schema Diagram

erDiagram
    DimCustomer ||--o{ FactSales : "filters"
    DimProduct ||--o{ FactSales : "filters"
    DimDate ||--o{ FactSales : "filters"
    DimLocation ||--o{ FactSales : "filters"

Relationships:

• DimDate[DateKey] 1 → * FactSales[DateKey]
• DimCustomer[CustomerID] 1 → * FactSales[CustomerID]
• DimProduct[ProductID] 1 → * FactSales[ProductID]
• DimLocation[LocationID] 1 → * FactSales[LocationID]

Filter direction:

• Single-direction from each dimension to the fact table.
• Avoid bidirectional filtering unless a specific many-to-many requirement demands it.

Additional practices:

• Mark DimDate as a date table.
• Hide primary and foreign keys from report view.
• Use explicit measures instead of implicit aggregations.
• Create bridge tables for many-to-many relationships.
• Use inactive relationships and USERELATIONSHIP for role-playing dimensions such as Order Date and Ship Date.
• Use Power Query merges in staging queries, not as a replacement for the model.

Justification

A flat table is acceptable only for very small or temporary solutions. A snowflake schema may be useful when dimensions are extremely large or already normalised, but it should be used selectively. The star schema remains the best balance of performance, simplicity, and scalability for Power BI.

Conclusion

Data modelling, relationships, and joins are not isolated technical topics. They are the foundation of a reliable Power BI solution. A flat table is easy but does not scale. A star schema is the standard because it balances performance, DAX simplicity, and maintainability. A snowflake schema can reduce redundancy but adds complexity. Fact tables store measurable events at a defined grain, while dimension tables provide descriptive context. Relationships connect these tables, with 1:* single-direction filtering being the default best practice. Power Query joins are transformation tools, while Power BI relationships are analytical tools. By choosing a star schema, keeping facts and dimensions separate, and using relationships carefully, you create a model that is faster, clearer, and easier to maintain.