DEV Community

Cover image for Power BI Data Modelling, Relationships & Joins
Kunu Wako
Kunu Wako

Posted on

Power BI Data Modelling, Relationships & Joins

#powerbi #datamodelling #datacleaning #dataanalysis

Introduction

Data modelling involves organizing tables, defining relationships between them, determining how filters move through the model, and deciding how business data should be represented for analysis. In a typical business intelligence environment, data may come from databases, Excel files, APIs, enterprise systems, or cloud platforms. These sources often contain related information in separate tables. For example, a retail company may maintain customers in one table, products in another, transactions in a sales table, and calendar information in a date table. Power BI must understand how these tables are related before it can correctly answer questions such as:

  • Which products generated the most revenue?
  • How much did each customer spend?
  • Which region recorded the highest sales?
  • How did monthly revenue change over time?

This article explores the major concepts involved in building such a model: flat tables, star schemas, snowflake schemas, fact and dimension tables, relationships, cardinality, filter direction, Power Query joins, and the difference between joins and model relationships.

1. Data Modelling in Power BI

As defined above, data modelling in Power BI is the process of structuring data into logically related tables so that it can be efficiently analyzed. A good model makes it easier for Power BI to determine how information from different tables should interact. Microsoft's Power BI guidance recommends models in which dimension tables are generally used for filtering and grouping, while fact tables are used for summarization. Microsoft also recommends maintaining fact tables at a consistent grain.

Why Data Modelling Matters

Reporting

Report developers can easily understand which fields should be placed in slicers, axes, tables, and measures. A clear model makes calculations easier to write.

For example:

Total Sales =
SUM(FactSales[SalesAmount])
Enter fullscreen mode Exit fullscreen mode

With a properly related DimProduct table, the same measure automatically works when the report is filtered by product, brand, or category.

Performance

Poorly designed models can contain unnecessary columns, repeated descriptive information, excessive relationships, or ambiguous filter paths. A simplified star-shaped model normally allows Power BI to evaluate queries more predictably.

Scalability

A model containing a million sales transactions should not need the customer's name, location, category, and other descriptive values repeatedly stored in every sales row. Dimensions allow descriptive data to be stored separately and reused.

Maintainability

Logical separation makes troubleshooting easier. If product descriptions change, developers know to investigate the product dimension instead of searching a massive sales table.

2. Flat Table Model

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

For example:

OrderID Date Customer County Product Category Quantity UnitPrice SalesAmount
1001 2026-09-01 Amina Nairobi Laptop Electronics 1 85000 85000
1002 2026-09-01 Brian Nakuru Mouse Accessories 2 1500 3000
1003 2026-09-02 Amina Nairobi Keyboard Accessories 1 4500 4500

The table combines transaction information with customer, location, product, and date attributes. There are no relationships because all the required information exists inside the same table.

Advantages

A flat table is simple to understand and can be appropriate for small datasets or quick exploratory analyses. It also avoids the need to create relationships because visualizations can reference columns directly from one table.

Disadvantages

The biggest problem is data repetition. If a customer makes 10,000 purchases, their name, county, and other customer details may appear 10,000 times. This creates:

  • unnecessary redundancy
  • larger models
  • more difficult maintenance
  • less intuitive organization
  • potential data quality problems
  • reduced scalability

A flat table can also make it difficult to distinguish between descriptive attributes and measurable business events.
A flat table can be useful when:

  • the dataset is small
  • there is only one analytical subject
  • a report is temporary
  • the source is already highly simplified
  • the model contains few columns
  • complex analytical relationships are unnecessary

3. Star Schema

A star schema consists of a central fact table surrounded by dimension tables. The fact table contains measurable business events while dimensions contain information describing those events. The structure resembles a star, which gives the schema its name.

Consider a retail sales model:

flowchart TB
    CUSTOMER["DimCustomer<br/>CustomerID<br/>CustomerName<br/>Segment"]
    PRODUCT["DimProduct<br/>ProductID<br/>ProductName<br/>Category"]
    DATE["DimDate<br/>DateKey<br/>Date<br/>Month<br/>Year"]
    LOCATION["DimLocation<br/>LocationID<br/>County<br/>Region"]

    SALES["FactSales<br/>SaleID<br/>CustomerID<br/>ProductID<br/>DateKey<br/>LocationID<br/>Quantity<br/>SalesAmount"]

    CUSTOMER --> SALES
    PRODUCT --> SALES
    DATE --> SALES
    LOCATION --> SALES

Advantages

A star schema provides:

  • clear separation of facts and dimensions
  • simple relationships
  • simpler DAX
  • predictable filter propagation
  • improved model readability
  • easier reporting
  • good scalability
  • reduced duplication of descriptive attributes

Disadvantages

Creating a star schema normally requires some preparation. Source systems frequently do not provide perfectly structured fact and dimension tables, so developers may need to:

  • clean data
  • create surrogate keys
  • remove duplicates
  • build dimension tables
  • combine multiple operational sources

A star schema can therefore require more initial modelling work than simply loading one flat spreadsheet.

Appropriate Situations

Star schemas are particularly suitable for:

  • sales analysis
  • financial reporting
  • inventory reporting
  • marketing analytics
  • customer analytics
  • operational dashboards
  • enterprise BI systems

4. Snowflake Schema

A snowflake schema extends a star schema by further normalizing dimensions into related tables. For example, instead of keeping category information inside DimProduct, product categories may have their own table.

flowchart LR
    CATEGORY["DimCategory<br/>CategoryID<br/>CategoryName"]
    PRODUCT["DimProduct<br/>ProductID<br/>ProductName<br/>CategoryID"]
    SALES["FactSales<br/>SaleID<br/>ProductID<br/>CustomerID<br/>DateKey<br/>SalesAmount"]
    CUSTOMER["DimCustomer"]
    DATE["DimDate"]

    CATEGORY --> PRODUCT
    PRODUCT --> SALES
    CUSTOMER --> SALES
    DATE --> SALES

Advantages

Snowflaking can:

  • reduce duplicated dimension attributes
  • represent complex organizational hierarchies
  • closely reflect normalized warehouse structures
  • allow some dimensions to be reused

Disadvantages

The model contains more tables and relationships. This can create:

  • more complex filter paths
  • a less intuitive Fields pane
  • more relationships to maintain
  • more work for report developers
  • potentially more complex queries

Snowflake structures can make sense where dimensions contain substantial reusable hierarchies or where the Power BI model intentionally reflects an existing enterprise data warehouse. However, unnecessary snowflaking should generally be avoided where dimension tables can reasonably be flattened.

5. Comparing the Three Modelling Approaches

Characteristic Flat Table Star Schema Snowflake Schema
Number of tables Usually one Several Several/many
Complexity Low initially Moderate Higher
Data redundancy High Low/moderate Lowest
Relationships Few/none Simple More complex
DAX usability Acceptable for simple models Excellent Can be more complex
Scalability Limited High High
Model readability Declines as table grows High Moderate
BI suitability Small/simple analysis Excellent Specialized scenarios
Maintenance Difficult at scale Good More relationships to manage

For most Power BI business intelligence solutions, the star schema offers the best balance between performance, usability and maintainability.

6. Fact Tables and Dimension Tables

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

Fact Tables

A fact table records business events.

Examples include:

  • a sale
  • an order
  • a payment
  • a website visit
  • a bank transaction
  • an inventory movement

Fact tables are normally the largest tables in analytical systems.

7. Dimension Tables

Dimension tables contain descriptive information used to classify, filter, and group business events. Microsoft describes dimension tables as tables that support filtering and grouping, whereas fact tables support summarization.

For example:

Typical dimensions include:

  • DimCustomer
  • DimProduct
  • DimDate
  • DimLocation
  • DimEmployee
  • DimSupplier

8. Measures Versus Descriptive Attributes

An easy way to understand the distinction is:

DIMENSIONS describe:
Who?
What?
Where?
When?

FACTS measure:
How many?
How much?
How long?
How often?
Enter fullscreen mode Exit fullscreen mode

For example:

CustomerName ─┐
ProductName  ─┼──► describe the sale
County       ─┤
Month        ─┘

Quantity     ─┐
Revenue      ─┼──► measure the sale
Profit       ─┘
Enter fullscreen mode Exit fullscreen mode

A report might therefore ask:

What was Total Sales by Product Category in Nairobi during August 2026?

In this question:

  • Total Sales = fact/measure
  • Product Category = dimension
  • Nairobi = dimension
  • August 2026 = dimension

9. Grain or Granularity

The grain of a fact table defines what one row represents. This must be decided before designing the table. For example:

One row in FactSales represents one product line on one customer transaction.

Suppose order ORD1001 contains three products.

The fact table would contain three rows:

OrderID ProductID Quantity
ORD1001 P01 2
ORD1001 P03 1
ORD1001 P09 4

The grain is therefore not one row per order but one row per product per order.

Mixing different grains in the same fact table can produce incorrect aggregations. Microsoft specifically recommends that fact tables load data at a consistent grain.

10. Practical Star Schema Example

Consider an electronics retailer.

The company wants to analyze:

  • sales by month;
  • sales by customer;
  • revenue by county;
  • sales by product category;
  • quantity sold by product.

A suitable model is:

flowchart TB
    C["DimCustomer<br/>CustomerID (PK)<br/>CustomerName<br/>Segment"]
    P["DimProduct<br/>ProductID (PK)<br/>ProductName<br/>Category"]
    D["DimDate<br/>DateKey (PK)<br/>Date<br/>Month<br/>Quarter<br/>Year"]
    L["DimLocation<br/>LocationID (PK)<br/>County<br/>Region"]

    F["FactSales<br/>SaleID<br/>CustomerID (FK)<br/>ProductID (FK)<br/>DateKey (FK)<br/>LocationID (FK)<br/>Quantity<br/>SalesAmount"]

    C -->|1 : many| F
    P -->|1 : many| F
    D -->|1 : many| F
    L -->|1 : many| F

Each dimension contains unique keys while those keys can occur repeatedly in the fact table.

11. Relationships in Power BI

A relationship defines how two tables are logically connected. Suppose customer information is stored in DimCustomer and transactions are stored in FactSales. Without a relationship, Power BI does not automatically know which sales belong to which customer.
The relationship might be:

12. Primary and Foreign Keys

Primary Key

A primary key uniquely identifies each record in a table.

For example:

DimCustomer

CustomerID CustomerName
C001 Amina
C002 Brian
C003 Carol

CustomerID contains unique values.

It can therefore identify a particular customer.

Foreign Key

A foreign key points to the corresponding key in another table.

FactSales

SaleID CustomerID Amount
S01 C001 2500
S02 C001 5000
S03 C002 1200
S04 C001 800

C001 appears multiple times because Amina can make several purchases.

Therefore:

DimCustomer                  FactSales

CustomerID                   CustomerID
-----------                  ----------
C001 ─────────────────────── C001
  │                          C001
  │                          C001
C002 ─────────────────────── C002
Enter fullscreen mode Exit fullscreen mode

This produces a one-to-many relationship.

13. Cardinality

Cardinality describes how rows from one table correspond to rows in another table. Power BI supports relationship types including:

  • one-to-many;
  • one-to-one;
  • many-to-many.

14. One-to-Many Relationship (1:*)

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

One row on the dimension side can correspond to many rows in the fact table.

DimCustomer          FactSales

C001 ─────────────── S001
  │                  S002
  └───────────────── S008

C002 ─────────────── S003
Enter fullscreen mode Exit fullscreen mode

The model relationship is:

For the 1 side to work correctly, the dimension key must contain unique values.

When to Use It

It is ideal for relationships such as:

Customer → Sales
Product  → Sales
Date     → Sales
Location → Sales
Enter fullscreen mode Exit fullscreen mode

This should normally be the dominant relationship pattern in a star schema.

15. One-to-One Relationship (1:1)

In a one-to-one relationship, each key occurs only once in each table.

Example:

Employee                         EmployeeSecurity
EmployeeID                       EmployeeID
----------                       ----------
E001 ─────────────────────────── E001
E002 ─────────────────────────── E002
E003 ─────────────────────────── E003
Enter fullscreen mode Exit fullscreen mode

Each employee has exactly one corresponding security profile.

Possible reasons for such a design include:

  • separating sensitive information
  • combining data from separate systems
  • temporarily extending an existing entity

However, a one-to-one relationship can sometimes indicate that the two tables should simply be combined. Microsoft notes that one-to-one relationships always filter in both directions and generally recommends considering alternative modelling designs before using them extensively.

16. Many-to-Many Relationships (:)

A many-to-many relationship occurs when multiple rows from one side can relate to multiple rows from the other. Consider students and courses. A student may register for several courses, and each course may contain several students.

Students                     Courses

Alice ─────────────┐      ┌─ Data Engineering
                   ├──────┤
                   │      └─ Machine Learning
                   │
Brian ─────────────┼──────── Data Engineering
                   │
                   └──────── Power BI
Enter fullscreen mode Exit fullscreen mode

In relational modelling, this is usually resolved with a bridge table:

flowchart LR
    S["DimStudent<br/>StudentID"]
    B["BridgeEnrollment<br/>StudentID<br/>CourseID"]
    C["DimCourse<br/>CourseID"]

    S -->|1 : many| B
    C -->|1 : many| B

The bridge table converts the conceptual many-to-many relationship into two one-to-many relationships. Direct many-to-many relationships are supported by Power BI, but they should be used deliberately because they can make filter behavior and calculations harder to reason about.

17. Referential Integrity

Referential integrity means that foreign-key values correctly reference corresponding records.

Suppose FactSales contains:

CustomerID = C900
Enter fullscreen mode Exit fullscreen mode

but there is no C900 in DimCustomer.

The sales row has no matching customer dimension record. Ideally, every sale has a matching customer ID. Maintaining referential integrity prevents unexplained or unmatched records and produces more reliable analytics.

18. Active and Inactive Relationships

Power BI relationships can be active or inactive. An active relationship is automatically used during filter propagation. It appears as a solid relationship line in Model View.

An inactive relationship is represented by a dashed line.

19. Filter Direction

Relationships do more than connect tables. They determine how filters propagate through the model.

Consider:

DimProduct
    1
    │
    ▼
    *
FactSales
Enter fullscreen mode Exit fullscreen mode

Suppose a user chooses:

Category = "Computers"
Enter fullscreen mode Exit fullscreen mode

in a slicer using DimProduct[Category].

Power BI first identifies products belonging to Computers.

DimProduct

P01 Laptop       Computers ✓
P02 Desktop      Computers ✓
P03 Mouse        Accessories
P04 Keyboard     Accessories
Enter fullscreen mode Exit fullscreen mode

The relationship then filters FactSales:

FactSales

P01  85,000 ✓
P01  72,000 ✓
P02  54,000 ✓
P03   1,500 ✗
P04   4,500 ✗
Enter fullscreen mode Exit fullscreen mode

The sales measure therefore evaluates only the transactions belonging to the selected products.

20. Single-Direction Filtering

Single-direction filtering allows filters to travel in one direction.

Typical star-schema behavior is:

DIMENSION          FACT
   1                *
   │                │
   └──────────────► │
Enter fullscreen mode Exit fullscreen mode

For example:

DimProduct ─────► FactSales
DimCustomer ────► FactSales
DimDate ────────► FactSales
Enter fullscreen mode Exit fullscreen mode

This is usually easy to understand:

A product filters sales.

It does not automatically mean:

Sales should filter the product dimension and continue through the model.

For one-to-many relationships, Power BI can use single-direction filtering from the 1 side toward the * side. Single-direction relationships are generally preferable where they satisfy the reporting requirement because the filter behavior remains predictable.

21. Bidirectional Filtering

A bidirectional relationship permits filters to move both ways.

DimProduct ◄──────► FactSales
Enter fullscreen mode Exit fullscreen mode

This can be useful in certain scenarios such as:

  • bridge-table designs
  • specific many-to-many requirements
  • dimension-to-dimension analysis
  • scenarios where slicers must display only values having corresponding fact data

However, bidirectional filtering should not be enabled everywhere. Microsoft recommends minimizing its use because it can negatively affect query performance and create confusing model behavior.

A major problem is ambiguous filter paths.

Consider:

          DimProduct
          ↕        ↕
       FactSales  Bridge
          ↕        ↕
          DimCustomer
Enter fullscreen mode Exit fullscreen mode

If several bidirectional paths exist, Power BI may have multiple ways of propagating the same filter. Microsoft notes that bidirectional relationships can negatively affect performance and can create ambiguous propagation paths. Power BI may prevent some ambiguous configurations from being created.

22. Joins in Power Query

A join combines rows from two tables based on matching columns. In Power Query, joins are normally created using:

Home → Merge Queries

Consider two tables.

Customers

CustomerID CustomerName
C001 Amina
C002 Brian
C003 Carol
C004 David

Orders

OrderID CustomerID Amount
O101 C001 5000
O102 C001 2500
O103 C003 7000
O104 C005 3000

Notice:

  • C001 appears in both tables.
  • C003 appears in both tables.
  • C002 and C004 have no orders.
  • order O104 belongs to C005, which is not in the Customers table.

These tables demonstrate each Power Query join type.

Power Query currently provides Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti join options through Merge operations.

23. Left Outer Join

Microsoft defines the Left Outer join as retaining every row from the left table and matching information from the right table. A Left Outer Join keeps:

All rows from the left table plus matching rows from the right table.
Conceptually:

LEFT TABLE              RIGHT TABLE
████████████        ┌───────────────┐
████████████────────█████████████████
████████████        └───────────────┘

Keep ALL left + matches from right
Enter fullscreen mode Exit fullscreen mode

With Customers as the left table:

CustomerID CustomerName OrderID Amount
C001 Amina O101 5000
C001 Amina O102 2500
C002 Brian null null
C003 Carol O103 7000
C004 David null null

Brian and David remain even though they have no orders.

Practical Use

Use a Left Outer Join when the left table represents the records that must be retained.

For example:

Keep every customer and attach their order details where available.

24. Right Outer Join

A Right Outer Join is effectively the reverse. It retains:

Every row from the right table plus matching information from the left table.

Using Orders as the right table:

CustomerID CustomerName OrderID Amount
C001 Amina O101 5000
C001 Amina O102 2500
C003 Carol O103 7000
C005 null O104 3000

The C005 order remains even though no corresponding customer exists. Use this when all records from the second table must survive the merge.

25. Full Outer Join

A Full Outer Join keeps every record from both tables.

Customers             Orders
┌─────────┐         ┌─────────┐
│█████████│█████████│█████████│
└─────────┘         └─────────┘

Everything from both sides
Enter fullscreen mode Exit fullscreen mode

Result:

CustomerID CustomerName OrderID Amount
C001 Amina O101 5000
C001 Amina O102 2500
C002 Brian null null
C003 Carol O103 7000
C004 David null null
C005 null O104 3000

No records are intentionally discarded.

Practical Use

This is useful for reconciliation tasks such as:

Compare two systems and retain all records regardless of whether a match exists.

26. Inner Join

An Inner Join keeps only records that match in both tables.

Customers           Orders
    ┌─────────────┐
────│ MATCH ONLY  │────
    └─────────────┘
Enter fullscreen mode Exit fullscreen mode

Result:

CustomerID CustomerName OrderID Amount
C001 Amina O101 5000
C001 Amina O102 2500
C003 Carol O103 7000

The following records disappear:

C002 Brian
C004 David
O104 / C005
Enter fullscreen mode Exit fullscreen mode

because they do not have matches on both sides.

Practical Use

Use an Inner Join when only matched records are relevant.

For example: Return only customers who have made purchases.

27. Left Anti Join

A Left Anti Join returns rows from the left table that do not have a match in the right table. Power Query describes this join as returning only left-side rows without corresponding right-side matches.

Result:

CustomerID CustomerName
C002 Brian
C004 David

These are the customers who have no matching orders.

Customers                     Orders

C001 ───────────────────────► MATCH     remove
C002 ──X                                KEEP
C003 ───────────────────────► MATCH     remove
C004 ──X                                KEEP
Enter fullscreen mode Exit fullscreen mode

Practical Use

Left Anti Join is extremely useful for data-quality analysis.

Examples include:

  • customers without orders
  • products never sold
  • employees without payroll records
  • database records missing from another system

28. Right Anti Join

A Right Anti Join returns rows from the right table that do not have matches in the left table.

With the example data:

OrderID CustomerID Amount
O104 C005 3000

This identifies an order whose CustomerID does not exist in the customer master table.

Practical Use

Right Anti Join can be used to discover:

  • orphan transactions
  • unmatched IDs
  • missing master-data records
  • synchronization problems between systems

29. Visual Summary of Power Query Join Types 

LEFT OUTER
Customers ◄──── priority
Keep all Customers + matching Orders


RIGHT OUTER
priority ────► Orders
Keep all Orders + matching Customers


INNER
Customers ∩ Orders
Keep matches only


FULL OUTER
Customers ∪ Orders
Keep everything


LEFT ANTI
Customers - Orders
Customers with NO matching Orders


RIGHT ANTI
Orders - Customers
Orders with NO matching Customers
Enter fullscreen mode Exit fullscreen mode

A compact comparison is:

Join Left Matches Left Non-Matches Right Matches Right Non-Matches
Left Outer ✓ ✓ ✓ ✗
Right Outer ✓ ✗ ✓ ✓
Full Outer ✓ ✓ ✓ ✓
Inner ✓ ✗ ✓ ✗
Left Anti ✗ ✓ ✗ ✗
Right Anti ✗ ✗ ✗ ✓

30. Power Query Joins vs Power BI Relationships

Joins and relationships are related concepts but perform fundamentally different tasks.

Power Query Merge

A merge happens during the data preparation stage.

Customers
   +
Orders
   │
   ▼
Power Query Merge
   │
   ▼
Combined Query
Enter fullscreen mode Exit fullscreen mode

A merge can physically bring columns from another query into the resulting table.

For example:

Before Merge 

FactSales
ProductID
Quantity
Amount
Enter fullscreen mode Exit fullscreen mode

and:

DimProduct
ProductID
ProductName
Category
Enter fullscreen mode Exit fullscreen mode

After merging and expanding the columns:

Sales
ProductID
ProductName
Category
Quantity
Amount
Enter fullscreen mode Exit fullscreen mode

The product attributes have effectively been added to the sales dataset.

31. Relationship

A Power BI relationship does not physically combine the tables.

Instead:

DimProduct                FactSales
ProductID                 ProductID
ProductName     1 ─── *   Quantity
Category                   Amount
Enter fullscreen mode Exit fullscreen mode

The tables remain separate.

The relationship simply tells the semantic model how they are connected and how filters can propagate between them.

MERGE
Two queries → physically combined result

RELATIONSHIP
Two model tables → remain separate but logically connected
Enter fullscreen mode Exit fullscreen mode

32. When Each Operation Happens

The typical sequence is:

1. DATA SOURCE
      │
      ▼
2. POWER QUERY
   - clean
   - transform
   - merge
   - append
      │
      ▼
3. DATA MODEL
   - relationships
   - cardinality
   - filter direction
      │
      ▼
4. DAX
   - measures
   - calculations
      │
      ▼
5. REPORT
   - visuals
   - slicers
   - dashboards
Enter fullscreen mode Exit fullscreen mode

A merge therefore occurs primarily during the transformation stage. A relationship exists within the semantic model stage.

33. When Should a Merge Be Used?

Merging is appropriate when two datasets logically need to become one query. Examples include:

Adding lookup information during transformation 

Transaction table
+
Exchange-rate table
=
Transactions with exchange rates
Enter fullscreen mode Exit fullscreen mode

Combining information representing the same logical entity 

Employee details
+
Employee extension attributes
=
Employee dimension
Enter fullscreen mode Exit fullscreen mode

Data validation

Anti joins can identify records missing from another system.

Preparing dimensions

Several source tables may need to be merged before producing a clean DimProduct table.

34. When Should a Relationship Be Used?

Relationships are preferable when tables represent different analytical roles.

For example:

DimCustomer
DimProduct
DimDate
DimLocation
FactSales
Enter fullscreen mode Exit fullscreen mode

These tables naturally represent a star schema.

Flattening all of them through repeated Power Query merges would destroy the logical separation between dimensions and facts.

Instead:

flowchart TB
    A["DimCustomer"] --> F["FactSales"]
    B["DimProduct"] --> F
    C["DimDate"] --> F
    D["DimLocation"] --> F

The model remains easy to understand and extend.

35. Problems With Excessive Merging

Suppose this star model:

DimCustomer ─┐
DimProduct ──┤
DimDate ─────┼──► FactSales
DimLocation ─┘
Enter fullscreen mode Exit fullscreen mode

is converted into:

MegaSalesTable
├── Sale fields
├── Customer fields
├── Product fields
├── Date fields
└── Location fields
Enter fullscreen mode Exit fullscreen mode

This can cause dimensions to be repeated across potentially millions of transaction rows. For example, the string 'Nairobi' may be repeated hundreds of thousands of times instead of being represented through a location dimension.

More importantly, the semantic distinction between:

WHO   = Customer
WHAT  = Product
WHEN  = Date
WHERE = Location
EVENT = Sale
Enter fullscreen mode Exit fullscreen mode

becomes less clear.

Keeping facts and dimensions separate therefore provides organizational as well as technical benefits.

36. Practical Comparison

Suppose management asks:

What were laptop sales in Nairobi during September?

Using a Star Schema

Filters originate in dimensions:

DimProduct
Category = Laptop
      │
      ▼
FactSales
      ▲
      │
DimLocation
County = Nairobi
      ▲
      │
DimDate
Month = September
Enter fullscreen mode Exit fullscreen mode

The same measure can remain:

Total Sales =
SUM(FactSales[SalesAmount])
Enter fullscreen mode Exit fullscreen mode

The relationships supply the filter context. This is one of the main strengths of a properly designed Power BI model: simple measures can answer complex analytical questions because the model handles context.

37. Recommended Power BI Model

For a typical business intelligence project, I would recommend a star schema. Consider a company analysing sales, customers, locations, products, and dates. The model could be:

flowchart TB
    DATE["DimDate<br/>DateKey<br/>Day<br/>Month<br/>Quarter<br/>Year"]
    CUSTOMER["DimCustomer<br/>CustomerID<br/>Customer<br/>Segment"]
    PRODUCT["DimProduct<br/>ProductID<br/>Product<br/>Category<br/>Brand"]
    LOCATION["DimLocation<br/>LocationID<br/>County<br/>Region"]

    SALES["FactSales<br/>SaleID<br/>DateKey<br/>CustomerID<br/>ProductID<br/>LocationID<br/>Quantity<br/>Revenue<br/>Cost"]

    DATE -->|1 to many| SALES
    CUSTOMER -->|1 to many| SALES
    PRODUCT -->|1 to many| SALES
    LOCATION -->|1 to many| SALES

The design would normally use:

Dimension     Relationship      Fact
---------     ------------      ----
DimDate       1 ───────── *     FactSales
DimCustomer   1 ───────── *     FactSales
DimProduct    1 ───────── *     FactSales
DimLocation   1 ───────── *     FactSales
Enter fullscreen mode Exit fullscreen mode

Filter direction would generally be Dimension to Fact, rather than bidirectional relationships everywhere.

38. Why the Star Schema Is Recommended

Query and Report Performance

The star design gives the engine a predictable structure in which relatively small dimensions filter a potentially large fact table. It also avoids unnecessary relationship chains.

DAX Simplicity

A measure such as:

Total Revenue =
SUM(FactSales[SalesAmount])
Enter fullscreen mode Exit fullscreen mode

can be reused for:

Revenue by Product
Revenue by Customer
Revenue by County
Revenue by Month
Revenue by Year
Enter fullscreen mode Exit fullscreen mode

without rewriting the measure. Filter context is supplied by the dimensions.

Model Readability

A developer opening Model View can immediately understand the analytical structure, rather than attempting to interpret dozens of arbitrary table connections.

Scalability

Additional dimensions can be introduced logically.

For example:

DimSalesperson
       │
       ▼
FactSales
Enter fullscreen mode Exit fullscreen mode

Likewise, another fact table can potentially reuse conformed dimensions:

              DimDate
              /     \
             ▼       ▼
       FactSales   FactBudget
Enter fullscreen mode Exit fullscreen mode

Reduced Data Redundancy

Customer and product descriptions do not have to be unnecessarily repeated throughout every fact row.

Maintainability

Changes can be isolated to appropriate tables.

Product changes  → DimProduct
Customer changes → DimCustomer
Calendar logic   → DimDate
Transactions     → FactSales
Enter fullscreen mode Exit fullscreen mode

Ease of Reporting

Report creators naturally choose:

Dimensions → slicers, axes and groupings
Facts      → measures and aggregations
Enter fullscreen mode Exit fullscreen mode

Predictable Filter Propagation

Single-direction filters from dimensions to the fact table create a clear path:

DimProduct ─────► FactSales
Enter fullscreen mode Exit fullscreen mode

instead of a network of unnecessary bidirectional relationships. Microsoft's Power BI modelling guidance similarly emphasizes star-schema concepts, consistent fact-table grain, and clear fact/dimension separation.

39. Recommended Design Principles

For most projects, I would therefore use the following approach:

  1. Build a star schema wherever practical.
  2. Store measurable business events in fact tables.
  3. Store descriptive attributes in dimension tables.
  4. Define a clear and consistent grain for every fact table.
  5. Create unique keys on dimension tables.
  6. Reference those keys as foreign keys in fact tables.
  7. Prefer one-to-many relationships between dimensions and facts.
  8. Prefer single-direction filtering from dimensions to facts.
  9. Use bidirectional relationships only when there is a specific analytical reason.
  10. Use many-to-many relationships carefully and introduce bridge tables when appropriate.
  11. Keep relationships active where they represent the default analytical path.
  12. Use inactive relationships or role-playing dimensions for alternative relationships such as Order Date and Ship Date.
  13. Use Power Query merges for data preparation, rather than as a replacement for proper dimensional modelling.
  14. Avoid turning an otherwise logical dimensional model into one flat table without a clear reason.

Conclusion

Flat tables can be effective for small and simple datasets, but their redundancy and lack of structure make them increasingly difficult to manage as data volumes and reporting requirements grow. Snowflake schemas provide additional normalization and can be useful when dimensions contain complex hierarchies, although the additional tables and relationships can make a Power BI model harder to use. For most business intelligence applications, a star schema provides the strongest balance between simplicity, scalability, performance, maintainability, and analytical flexibility.
Power Query joins solve a different problem. They transform and physically combine query data before or during loading, whereas Power BI relationships preserve separate tables and establish logical connections within the semantic model. Understanding when to merge data and when to relate tables is therefore essential. Ultimately, good Power BI development depends on designing the model before designing the dashboard. When the underlying schema, grain, keys, relationships, and filter directions are correct, DAX becomes simpler, reports become easier to build, and the resulting analytics are both more reliable and easier to maintain.

Top comments (0)