Power BI Data Modeling: Relationships, Star Schema, Fact and Dimension Tables, and Power Query Joins

The problem before the visual

A Power BI dashboard can look impressive, but the model underneath can be complete chaos. The visual is what the user sees, but its accuracy and flexibility depend on how the data is structured before reporting begins.

Consider a Premier League dataset, the business process being analysed is the Premier League match intended to answer questions such as: Which teams perform better at home? Which teams struggle away? Do match statistics change during different periods of the season? Are there patterns in fouls, cards, or referee decisions?

All of these questions can be answered with visuals, but the more important question comes first:

How should the data be structured so that the same model can support different reports and analytical questions?

That is where data modelling begins.

What is data modelling?

Data modelling is the process of organising data into logical structures and defining how those structures relate to each other.

Source data is not always arranged in the best way for analysis. It is usually structured according to how it was collected, exported, or stored. Data modelling looks beyond that original structure and identifies the events in the data, the information that describes those events, and the relationships required to analyse them correctly.

A good model therefore does more than separate columns into different tables. It creates a structure that Power BI can use efficiently for reporting, filtering and calculations.

Why does data modelling matter?

As more information is added to a report, a poorly structured model becomes harder to maintain. Descriptive values can be repeated unnecessarily, tables can become increasingly wide, and calculations may begin carrying logic that should have been handled by the model itself.

Performance is another consideration. Power BI needs to load, compress, scan, and filter the model before producing results. Unnecessary duplication and complicated relationship paths can make that work harder.

The model also affects DAX. When relationships and table responsibilities are clear, calculations can use the existing model structure. When the structure is poor, DAX often has to compensate for missing or confusing modelling logic.

Golden rule: A bad data model can lead to bad and complicated DAX.

Maintainability is equally important. A model should make sense when another analyst opens it later. The purpose of each table, the central business event, and the main relationship paths should be understandable without having to reverse-engineer the entire report.

A useful modelling question is therefore:

How can this model serve multiple reports?

Parting shot

Fact tables, dimension tables and grain

In dimensional modelling, tables normally serve different purposes. Two of the most important roles are the fact table and the dimension table.

A fact table stores the business events, transactions, or activities being analysed. It normally contains measurements together with keys that connect those events to descriptive information.

A dimension table stores the descriptive information that gives context to those events. Dimensions are commonly used for filtering, grouping, and labelling data in reports.

Before deciding what belongs in the fact table, the grain must be established.

Grain describes the level of detail represented by one row of the fact table. This matters because every measure is interpreted at that level. A table where one row represents an individual event behaves very differently from one where each row represents a monthly summary.

The simplest way to identify grain is to ask:

What does one row represent?

That answer should be clear enough to state in one sentence.

Three ways to organise data: Flat, Star and Snowflake

Once the roles of the tables are understood, the next question is how those tables should be arranged.

Flat schema

A flat schema keeps descriptive information and measurements together in one large table.

Its main advantage is simplicity. There are fewer tables to manage, no relationships are required, and the structure can be convenient for a small or temporary analysis.

The problem appears as the data grows. Descriptive values are repeated across many rows, redundancy increases, and the table can become increasingly difficult to maintain. Calculations may also become more complicated because there is no clear separation between descriptive attributes and measurable events.

A flat schema is therefore not automatically wrong. It can be appropriate when the analysis is small and unlikely to grow. The problem is using the same structure for a model that needs to scale.

Star schema

A star schema separates the business event from the descriptive information surrounding it. The fact table sits in the centre while dimension tables connect directly to it.

                 DimDate
                    |
                    |
DimTable ------- FactTable ------- DimTable
                    |
                    |
                 DimTable

The central fact table stores events and measurements while dimensions provide the context required to analyse them.

One of the main advantages of a star schema is that the filter paths are relatively easy to understand. A selection from a dimension can travel through the relationship to the fact table, where the relevant measures are calculated.

This structure can also make DAX easier to follow because the model already separates descriptive attributes from business events.

The trade-off is that real-world data does not always form a perfect star. A dimension may need to play more than one role, or the analytical problem may contain relationships that require additional handling. A star schema provides a clear structure, but it still has to reflect the actual problem.

Snowflake schema

A snowflake schema begins with the same fact-and-dimension structure but normalises one or more dimensions into additional related tables.

                 DimDate
                    |
                    |
DimTable ------- FactTable ------- DimTable
   |
   |
SubDimension

This can reduce repetition inside large dimensions by moving repeated descriptive information into separate tables.

The trade-off is additional complexity. More tables create more relationships, and filters may need to travel through several tables before reaching the fact table. This can make the model harder to read, maintain, and troubleshoot.

Snowflaking therefore needs a reason. Reduced redundancy should provide enough value to justify the additional relationship complexity.

How do we choose a schema?

Knowing that flat, star, and snowflake schemas exist is not enough. The model still needs to be selected according to the analytical problem.

Star schema may be widely recommended, but that alone is not a sufficient reason to use it.

Instead, the model can be evaluated against questions such as:

These questions make the schema choice a design decision rather than a rule.

Applying the concepts to the Premier League data

The Premier League dataset used in this project contains match information such as date, home and away teams, referee, goals, results, shots, shots on target, fouls, corners and cards.

Before creating tables, the grain needs to be established.

One row represents one Premier League match.

That makes the match the central business event.

FactMatches

FactMatches records the match itself together with measurable statistics such as goals, shots, shots on target, fouls, corners, cards, and results.

DimTeam

DimTeam contains descriptive information about the teams. This allows teams to be used consistently for filtering and grouping while the match events remain in the fact table.

DimDate

DimDate describes when matches occurred. It can contain attributes such as day, month, quarter, and year, which can then be reused across different reports.

DimReferee

DimReferee describes the referees associated with the matches. This provides a clear location for referee information and allows matches to be analysed or filtered by referee.

The resulting structure begins to resemble a star schema:

                 DimDate
                    |
                    |
DimTeam -------- FactMatches -------- DimReferee

At this stage, however, separate tables alone do not create a complete model. Power BI still needs a reliable way to determine how records from one table correspond to records in another.

Relationships in Power BI

A relationship defines how records in one table correspond to records in another.

Relationships are necessary when information has been distributed across several tables. They allow those tables to remain separate while still interacting during filtering and analysis.

For example, DimTeam contains descriptive information about a team while FactMatches records the matches involving that team. The relationship tells Power BI how those records correspond.

Relationships therefore affect much more than the appearance of Model View. They influence filter propagation, calculation context, and how data from different tables can be analysed together.

Before relationships can be created correctly, the tables need suitable keys.

Keys

A primary key uniquely identifies a record in a table.

A foreign key exists in another table and refers back to that record. The foreign key can repeat because the same entity may participate in many events.

The important point is that a relationship should not be created simply because two columns have similar names. The values must support the intended relationship.

For a normal one-to-many relationship:

• the key on the one side should be unique;
• the corresponding foreign key can repeat on the many side;
• the values on the many side should have valid matches on the one side.

A useful question is:

What makes a column suitable for a relationship?

The answer depends on the meaning of the column, its uniqueness, and whether the values correctly match across the tables.

Applying keys to the football model

The Premier League source contains team and referee names but does not provide ready-made model IDs such as TeamID or RefereeID.

This does not prevent dimensional modelling. Once the entities have been identified, model keys can be created to provide stable identifiers.

For DimTeam:

For DimReferee:

DimDate can use a DateKey, while FactMatches stores the corresponding foreign keys.

The key exists because the model needs a reliable way to identify and relate records.

Cardinality

Cardinality describes how many records on one side of a relationship can correspond to records on the other side.

One-to-many

A one-to-many relationship means one record on the one side can correspond to several records on the many side.

This is common in dimensional modelling because a dimension contains one record for an entity while that same entity may appear in many fact-table events.

One-to-one

A one-to-one relationship means one record in one table corresponds to exactly one record in another table.

The relationship key is unique on both sides.

This can be valid, but it should have a clear purpose. If both tables have the same grain and are always used together, separating them may introduce complexity without providing much analytical value.

Many-to-many

A many-to-many relationship allows values to repeat on both sides.

This can represent a legitimate analytical problem, but it creates more complicated filter behaviour. It should not be used simply because duplicate values appear unexpectedly.

If a table that was expected to provide unique values contains duplicates, the grain and table design should be checked before choosing many-to-many.

Cardinality in the Premier League model

The relationship between DimDate and FactMatches is one-to-many because one date can contain several matches:

DimDate[DateKey]  1 -------- * FactMatches[DateKey]

The same principle applies to referees. One referee can officiate several matches:

DimReferee[RefereeID]  1 -------- * FactMatches[RefereeID]

The current model does not naturally require a one-to-one or many-to-many relationship. Understanding those relationship types includes knowing when they are unnecessary.

The team dimension introduces a different challenge. One team can participate in many matches, but each match contains two separate team roles:

HomeTeamID
AwayTeamID

Both refer to the same DimTeam[TeamID].

                     DimTeam
                    /       \
                   /         \
                  v           v
           HomeTeamID     AwayTeamID
                   \         /
                    \       /
                    FactMatches

The cardinality remains one-to-many, but the same dimension is now being used in two different roles.

Active and inactive relationships

Power BI can contain more than one possible relationship between tables, but only one relationship can normally act as the default filtering path between the same two tables.

An active relationship is used automatically during filtering and calculations.

An inactive relationship still exists in the model but is not used automatically. It can be activated when a specific calculation requires that alternative relationship.

This becomes important when the same dimension needs to describe the fact table in more than one role.

Active and inactive relationships in the football model

DimTeam contains one list of teams, while FactMatches contains both HomeTeamID and AwayTeamID.

Both are valid references to the same dimension:

                     DimTeam
                    /       \
             active           inactive
                  /             \
                 v               v
           HomeTeamID       AwayTeamID
                   \         /
                    FactMatches

This is a role-playing dimension. The entity is still a team, but its role changes depending on whether it appears as the home team or away team.

One relationship can provide the normal active path, while the second remains available for calculations that need the alternative team role.

The football model therefore provides a practical reason for active and inactive relationships rather than treating them as two settings to memorise.

Referential integrity

Referential integrity means that every foreign-key value should have a valid corresponding record in the related table.

If a fact table contains a foreign key that cannot be found in the dimension, the event still exists, but the model cannot retrieve the descriptive information expected from that dimension.

Relationships therefore depend on the quality of the keys used to create them.

A relationship does not correct an invalid reference. It exposes the consequences of that invalid reference during analysis.

Referential integrity in the Premier League model

Every:

HomeTeamID
AwayTeamID
RefereeID
DateKey

in FactMatches should have a corresponding value in its related dimension.

If FactMatches contains a team key that does not exist in DimTeam, that match cannot be fully described through the team dimension.

The same principle applies to referees and dates.

This creates an important connection between data quality and data modelling:

Relationships do not repair bad keys. They rely on valid keys.

Anti joins provide one way to test these relationships before relying on them in the final model.

Filter direction

A relationship also determines how filters can propagate between tables.

With single-direction filtering, a filter travels in one direction. In a typical dimensional model, this is normally from the dimension to the fact table:

Dimension
    |
    v
Fact

This produces a predictable analytical path. A descriptive value is selected in the dimension, and the corresponding fact records are filtered.

With bidirectional filtering, filters can travel in both directions:

Table A <----> Table B

Bidirectional filtering can be useful, but it should be introduced carefully. As the number of relationships increases, allowing filters to travel both ways can create multiple filter paths, ambiguity, and model behaviour that becomes harder to troubleshoot.

The decision should therefore be based on analytical requirements rather than simply using bidirectional filtering because it makes a particular visual work.

Filter direction in the Premier League model

The predictable default for this model is:

Dimension
    |
    v
FactMatches

Selecting a referee in DimReferee should filter the matching records in FactMatches. Selecting a date should do the same through DimDate.

The active team relationship follows the same principle.

Single-direction filtering from the dimensions to FactMatches therefore provides the clearest default design. Bidirectional filtering would only be introduced where a specific reporting requirement justifies the additional complexity.

A join is not a relationship

Relationships and joins both involve matching data, but they perform different jobs.

A Power Query Merge combines matching data while the queries are being transformed.

A Power BI relationship keeps the tables separate and defines how they interact after they have been loaded into the semantic model.

This distinction matters because excessive merging can undo the separation created by dimensional modelling. If every dimension is merged back into the fact table, the model can gradually return to a flat structure.

A merge should therefore solve a transformation problem, while a relationship should solve a modelling problem.

Join types

Power Query provides six main join types. The easiest way to understand them is to ask:

Which records does the join retain?

Left Outer Join

A Left Outer Join keeps every row from the first table together with matching information from the second table. If no matching row exists on the right, the left-side record remains.

Right Outer Join

A Right Outer Join keeps every row from the second table together with matching information from the first table.

Full Outer Join

A Full Outer Join keeps all rows from both tables. Matching records can be combined, while unmatched records from either side remain visible.

Inner Join

An Inner Join keeps only records that have a match in both tables. Unmatched records from either side are removed from the result.

Left Anti Join

A Left Anti Join keeps rows from the first table that do not have a matching row in the second table.

Right Anti Join

A Right Anti Join keeps rows from the second table that do not have a matching row in the first table.

The anti joins are particularly useful for validation because they answer a different question:

Which records failed to match?

Six joins using the football data

The join behaviour becomes easier to see using a small sample of the football model.

FactMatches sample

DimTeam sample

T009, T011,, and T020 exist in both tables. T021 exists only in the fact sample, while T099 exists only in the dimension sample.

Left Outer

If FactMatches is the left table, all four match records remain. The record containing T021 is still preserved even though no matching team exists in DimTeam.

This type of join is useful when adding descriptive or key information to a fact query without wanting an unsuccessful lookup to remove the original event.

Right Outer

If DimTeam is the right table, every team record remains, including T099.

The join therefore preserves the population from the right-side table.

Full Outer

A Full Outer Join retains all records from both tables.

Both T021 and T099 remain visible, making this join useful when comparing two datasets and identifying mismatches on either side.

Inner

An Inner Join retains only:

T009
T011
T020

These are the values that exist in both tables.

The important risk is that M004 disappears from the result because its team key does not match. If the objective is to preserve all match events, that behaviour matters.

Left Anti

A Left Anti Join returns:

This answers:

Which match record contains a team that cannot be found in DimTeam?

That makes Left Anti particularly useful for testing referential integrity.

Right Anti

A Right Anti Join returns:

This answers:

Which team exists in the dimension but does not appear in this match sample?

Parting shot

Anti joins are more than additional options in the Merge Queries window. They can be useful data-quality tools because they make unmatched records visible.

Where Merge Queries fits into the football model

The original match data stores team names, while DimTeam contains the corresponding model key.

For example:

HomeTeam = Arsenal

while the dimension contains:

FactMatches requires the corresponding HomeTeamID.

A Power Query merge can therefore match:

FactMatches[HomeTeam]
        ↓
DimTeam[TeamName]

and return the related TeamID.

That value can then become:

HomeTeamID

The process can be repeated for AwayTeamID and for the referee key.

Once the tables are loaded into the model, a relationship can then be created:

DimTeam[TeamID]
      1
      |
      *
FactMatches[HomeTeamID]

The two operations have different purposes:

The merge builds or retrieves the foreign key. The relationship tells Power BI how to use that key during analysis.

The model that fits the Premier League data

After considering the grain, table roles, schemas, keys, cardinality, and filtering requirements, a star-style structure provides the clearest fit for the model.

                         DimDate
                            |
                            |
                            v
DimTeam  -------------> FactMatches <------------- DimReferee
    \                       ^
     \______________________|
        second team role

The normal design uses one-to-many relationships from the dimensions to FactMatches, together with single-direction filtering from dimension to fact.

DimTeam requires additional handling because it performs both the home-team and away-team roles.

The star-style structure is therefore not selected simply because it is commonly recommended. It fits the grain of the dataset, the analytical questions being asked, and the relationship complexity required by the model.

Lessons learned

1. The source structure is not the analytical model

The way data arrives does not determine how it must be modelled. Source data is designed around collection and storage, while the Power BI model should be designed around analysis.

2. Grain comes before measures

A measure only has meaning when the level of detail underneath it is understood. In this model, establishing that one row represents one match provides the foundation for every match statistic that follows.

3. Keys need a purpose

IDs should not be created simply because dimensional models use keys. The entities and grain should be understood first, after which the appropriate model keys can be created.

4. Relationships are part of the analytical logic

Relationship lines in Model View are not decoration. Cardinality, active and inactive relationships, and filter direction all affect how Power BI evaluates a report.

5. Data quality becomes a modelling issue

Once dimensions and facts depend on matching keys, missing or invalid values can affect the model itself. Referential integrity therefore connects data preparation directly to reporting reliability.

6. Joins and relationships solve different problems

A merge prepares and combines data during transformation. A relationship allows separate model tables to interact during analysis. Using one does not make the other unnecessary.

7. Schema selection is a design decision

Flat, star, and snowflake schemas each have advantages and trade-offs. The correct structure depends on grain, analytical requirements, performance, maintainability, reporting needs, and relationship complexity.