Data Relationships, Data Modelling Schemas, and Joins in Power BI

Introduction

A successful Power BI solution depends on more than creating attractive charts and dashboards. The underlying data model determines how accurately data can be analysed, how easily DAX measures can be written, and how efficiently reports perform as the volume of data increases. Data modelling is therefore one of the most important stages in building a Power BI solution.

A well-designed model organizes data into logical tables, defines relationships between those tables, and controls how filters move through the model. It also reduces unnecessary duplication and makes reports easier to understand and maintain.

This article explains three common modelling approaches:Flat Table, Star Schema, and Snowflake Schema and examines fact and dimension tables, relationships, filter direction, Power Query joins, and the difference between merging tables and creating model relationships.

Flat Table

A flat table stores most or all required information in one table.

🟩 SALES
┌─────────┬──────────┬─────────┬────────────┬────────────┐
│ OrderID │ Customer │ Product │ Date       │ SalesAmount│
├─────────┼──────────┼─────────┼────────────┼────────────┤
│ 1001    │ John     │ Laptop  │ 01-Jan-26  │ $1,200     │
│ 1002    │ Mary     │ Monitor │ 02-Jan-26  │ $500       │
│ 1003    │ John     │ Mouse   │ 03-Jan-26  │ $50        │
└─────────┴──────────┴─────────┴────────────┴────────────┘

The major advantage is simplicity. There are no relationships to configure, making the approach suitable for small datasets, prototypes, or simple reports.

However, flat tables often contain substantial data redundancy. Customer names, product descriptions, locations, and other attributes may be repeated across thousands or millions of transactions. This increases model size and makes changes harder to manage.

For Power BI, a flat table can work well for small datasets, but it becomes less attractive as data volume and analytical requirements increase.

Star Schema

A star schema separates business events into a central fact table and descriptive information into surrounding dimension tables.

                         🟢 DimDate
                             │
                             │ 1:*
                             ▼
🔵 DimCustomer ── 1:* ── 🟣 FactSales ── *:1 ── 🟠 DimProduct
                             ▲
                             │
                             │ 1:*
                             │
                       🔴 DimLocation

The fact table contains transactions, while dimensions describe those transactions. This is generally the preferred structure for Power BI because it provides clear filter paths, good performance, and straightforward DAX.

Its main disadvantages are that it requires more modelling knowledge and relationships must be configured correctly.

Snowflake Schema

A snowflake schema extends the star schema by further normalizing dimensions.

🟢 DimCountry
     │
     │ 1:*
     ▼
🔴 DimLocation ── 1:* ── 🟣 FactSales ── *:1 ── 🟠 DimProduct
                           ▲
                           │
                           │ *:1
                           │
                     🔵 DimCustomer

For example, instead of storing Country directly in DimLocation, a separate DimCountry table may contain country information.

Snowflake schemas can reduce redundancy, especially in large hierarchical dimensions, but they introduce additional relationships and longer filter paths. Consequently, they can make DAX and troubleshooting more complicated.

Comparison: A flat table is simple but potentially redundant; a star schema balances simplicity and performance; a snowflake schema provides greater normalization but increases model complexity.

---

2. Fact Tables and Dimension Tables

The distinction between fact and dimension tables is fundamental to dimensional modelling.

Fact Tables

A fact table records measurable business events. It normally contains:

• Foreign keys to dimensions
• Transaction or event identifiers
• Numeric values
• Quantities
• Costs
• Revenue
• Discounts
• Other measures

Examples include:

• FactSales
• FactOrders
• FactTransactions

The most important concept when designing a fact table is grain or granularity. Grain defines exactly what one row represents.

For example:

One row in FactSales represents one product sold on one sales transaction.

If this rule is unclear, calculations can become inconsistent because different rows may represent different levels of detail.

Dimension Tables

Dimension tables contain descriptive information used to categorize and filter facts.

Examples include:

• DimCustomer
• DimProduct
• DimDate
• DimLocation

For example, DimProduct might contain ProductID, ProductName, Category, Brand, and ProductGroup.

A typical business model might therefore look like:

                 🟢 DimDate
                     │
                     │
🔵 DimCustomer ── 🟣 FactSales ── 🟠 DimProduct
                     │
                     │
                 🔴 DimLocation

The fact table answers questions such as "How much was sold?", while dimensions answer "Who bought it?", "What was sold?", "When?", and "Where?"

---

3. Relationships in Power BI

A relationship defines how two tables are connected using a common column. Relationships are necessary because business information is normally distributed across multiple tables.

For example, DimCustomer contains customer details while FactSales contains transactions. CustomerID connects the two.

One-to-Many (1:*)

This is the most common relationship in a Power BI star schema.

🟢 DimCustomer                    🟣 FactSales
CustomerID (Unique)   1 ─────── * CustomerID (Repeated)

One customer can have many sales transactions. Therefore, CustomerID occurs once in DimCustomer but can occur many times in FactSales.

This should normally be the default relationship for dimension-to-fact modelling.

One-to-One (1:1)

Each record in one table corresponds to exactly one record in another.

🟢 CustomerProfile
CustomerID     1 ───── 1     🟣 CustomerAccount
CustomerID

This may be appropriate when two tables contain different sets of attributes for exactly the same entities. However, it should not be used simply because two tables happen to contain unique IDs.

If the tables represent the same business entity, combining them may sometimes be simpler.

Many-to-Many (:)

Multiple records in one table can correspond to multiple records in another.

🟠 Students       * ───── *       🟣 Courses

For example, one student can take many courses, while each course can contain many students.

Many-to-many relationships can be valid, but they should be used carefully because they can create ambiguous filtering and unexpected aggregation results. A bridge table is often preferable for complex many-to-many scenarios.

Keys and Referential Integrity

A primary key uniquely identifies a row in a table. A foreign key references a key in another table.

For example:

DimCustomer
CustomerID
101
102
103

versus:

FactSales
CustomerID
101
101
102
101
103

DimCustomer[CustomerID] must be unique, while FactSales[CustomerID] can repeat because the same customer can make multiple purchases.

Referential integrity means foreign-key values should correctly correspond to records in the related dimension. Missing or unmatched keys can produce blank or unexpected results in reports.

Power BI relationships can also be active or inactive. An active relationship is used automatically when filters propagate. An inactive relationship can be activated within specific DAX calculations using functions such as USERELATIONSHIP.

---

4. Filter Direction

Filter direction determines how filtering moves between related tables.

Single-Direction Filtering

This is generally recommended for star schemas.

🟢 DimProduct ────────► 🟣 FactSales
       Filter              Results

Suppose DimProduct[Category] = "Electronics" is selected. The filter travels from DimProduct to FactSales, restricting sales to electronics products.

This creates a predictable model with clear filter paths.

Both/Bidirectional Filtering

Bidirectional filtering allows filters to move in both directions.

🟢 DimProduct ◄──────► 🟣 FactSales

Although useful in specific scenarios, it should not be enabled unnecessarily. With several related tables, bidirectional relationships can create ambiguous filter paths, unexpected results, and additional model complexity.

For most dimensional models, filtering from dimensions toward facts provides the clearest design.

---

5. Joins in Power Query

A join combines information from two tables based on matching columns. In Power Query, this operation is performed using Merge Queries.

Consider:

Customers

CustomerID	CustomerName
1	John
2	Mary
3	David

Orders

OrderID	CustomerID	Amount
101	1	500
102	1	200
103	2	300
104	4	150

Customer 4 does not exist in the Customers table.

Inner Join

Returns only matching records from both tables.

Customers ∩ Orders
        🟢
      MATCHES

Expected customers: John and Mary.

CustomerID	CustomerName	OrderID	Amount
1	John	101	500
1	John	102	200
2	Mary	103	300

Left Outer Join

Keeps all records from the left table and matching records from the right table.

🟢 Customers  █████████████
🟣 Orders          ███████

David remains even though he has no order.

Right Outer Join

Keeps all records from the right table and matching records from the left table.

Order 104 remains even though Customer 4 has no matching customer record.

Full Outer Join

Keeps all records from both tables.

🟢 Customers  ████████
              ███████████  🟣 Orders

The result includes customers without orders and orders without matching customers.

Left Anti Join

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

Customers − Orders

Result:

CustomerID	CustomerName
3	David

This is useful for identifying customers who have never placed an order.

Right Anti Join

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

Orders − Customers

Result:

OrderID	CustomerID	Amount
104	4	150

This is particularly useful for data-quality checks because it identifies orders containing an invalid or missing customer reference.

---

6. Power Query Joins vs Power BI Relationships

Although both concepts connect data, a Power Query merge and a Power BI relationship serve different purposes.

Power Query Merge

A merge happens during the data preparation/transformation stage.

Customers + Orders
       ↓
 Power Query Merge
       ↓
Combined Table

The result can physically bring columns from one table into another. For example, CustomerName can be added to an Orders query.

Power BI Relationship

A relationship is created in the data model after data loading.

DimCustomer ───── 1:* ───── FactSales

The tables remain separate. Power BI understands that the tables are connected and uses the relationship for filtering and calculations.

Therefore:

Merge combines data; relationships connect data logically.

A merge is appropriate when a column is genuinely required in the same query, when transforming source data, or when preparing a table for a specific analytical requirement.

However, excessive merging can create very wide tables, duplicate descriptive information, increase redundancy, and make the model harder to maintain.

Keeping fact and dimension tables separate is usually preferable in a BI model because each table has a clear responsibility and relationships allow the same dimension to be reused across multiple facts.

---

7. Recommended Power BI Model

For a typical business intelligence project, I would recommend a Star Schema with a central fact table and several dimension tables.

                         🟢 DimDate
                            │
                            │ 1:*
                            ▼
🔵 DimCustomer ── 1:* ── 🟣 FactSales ── *:1 ── 🟠 DimProduct
                            ▲
                            │
                            │ *:1
                            │
                       🔴 DimLocation

The recommended relationship design would normally be:

• One-to-many relationships from dimensions to facts.
• Single-direction filtering from dimensions toward fact tables.
• Unique primary keys in dimension tables.
• Foreign keys in fact tables.
• Clearly defined fact-table grain.
• Separate fact and dimension tables rather than unnecessary merges.
• Inactive relationships only where there is a genuine business requirement.

The star schema is preferable because it provides a strong balance between performance, simplicity, scalability, and maintainability. It reduces redundancy compared with a flat table while avoiding much of the complexity introduced by a heavily normalized snowflake structure.

It also makes DAX easier to understand. Measures such as:

Total Sales = SUM(FactSales[SalesAmount])

can naturally respond to filters from DimCustomer, DimProduct, DimDate, or DimLocation.

From a reporting perspective, the model is intuitive: users select descriptive fields from dimensions and measures from the fact table. From a performance perspective, the model avoids unnecessary duplication and provides straightforward filter propagation. From a maintenance perspective, changes to customer, product, or location attributes can be managed in their respective dimensions rather than repeatedly across transaction records.

Conclusion

Data modelling is the foundation of an effective Power BI solution. While flat tables can be useful for small and simple datasets, a star schema is generally the strongest choice for production BI models. It separates measurable business events from descriptive attributes, creates predictable relationships, and supports efficient DAX calculations.

Understanding relationship cardinality is equally important. One-to-many relationships should normally form the backbone of a dimensional model, while one-to-one and many-to-many relationships should be introduced only when the business structure justifies them. Single-direction filtering should generally be preferred because it creates predictable filter propagation.

Power Query joins serve a different purpose from model relationships. Joins are primarily used during data preparation to combine or compare datasets, whereas relationships connect tables within the analytical model without physically combining them.