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
| Without deliberate modelling | With a deliberate data model |
|---|---|
| Information can be repeatedly stored | Repetition can be reduced |
| Reporting logic becomes harder to follow | Tables have clearer responsibilities |
| DAX may compensate for structural problems | DAX can use the model structure |
| Growth can increase complexity quickly | The model is easier to extend |
| Harder to maintain | Easier to understand and maintain |
| Often designed around one report | Can support multiple reporting questions |
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.
| Dimension table | Fact table |
|---|---|
| Describes the event | Records the event |
| Contains descriptive attributes | Contains events and measurements |
| Commonly used for filtering and grouping | Commonly used for calculations |
| Usually contains unique members | Foreign keys can repeat |
| Gives context to the numbers | Contains the numbers being analysed |
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:
| Consideration | Question |
|---|---|
| Grain | What does one row represent? |
| Analytics | What questions must the model answer? |
| Facts | What events or numbers are being measured? |
| Dimensions | What describes those events? |
| Relationships | How complicated will the connections become? |
| DAX | Will calculations remain understandable? |
| Performance | Is there unnecessary duplication or relationship complexity? |
| Maintainability | Can another person understand the model easily? |
| Scalability | Can more data be added later? |
| Reporting | Can users filter and analyse the data easily? |
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:
| TeamID | TeamName |
|---|---|
| T001 | Bournemouth |
| T002 | Newcastle |
| T003 | Fulham |
| ... | ... |
For DimReferee:
| RefereeID | RefereeName |
|---|---|
| R001 | A Taylor |
| R002 | C Pawson |
| R003 | S Barrott |
| ... | ... |
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.
| Active relationship | Inactive relationship |
|---|---|
| Used automatically | Not automatically used |
| Default analytical path | Alternative analytical path |
| Solid line in Model View | Dashed line in Model View |
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.
| Power Query Merge | Power BI relationship |
|---|---|
| Data preparation | Data modelling |
| Combines matching information | Keeps tables separate |
| Can add columns to a query | Defines analytical interaction |
| Changes the resulting query | Does not physically combine tables |
| Happens during transformation | Operates in the semantic model |
| Useful for shaping and lookup operations | Useful for filtering and calculations |
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
| MatchID | TeamID |
|---|---|
| M001 | T009 |
| M002 | T011 |
| M003 | T020 |
| M004 | T021 |
DimTeam sample
| TeamID | TeamName |
|---|---|
| T009 | Arsenal |
| T011 | Chelsea |
| T020 | Liverpool |
| T099 | Example FC |
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:
| MatchID | TeamID |
|---|---|
| M004 | T021 |
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:
| TeamID | TeamName |
|---|---|
| T099 | Example FC |
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:
| TeamID | TeamName |
|---|---|
| T009 | Arsenal |
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.
| Requirement | Why the model fits |
|---|---|
| Reporting | Team, referee, and date provide clear reporting dimensions |
| DAX | Calculations can use understandable filter paths |
| Performance | Descriptive information does not need to be repeated with every match |
| Readability | Facts and dimensions are easy to identify |
| Scalability | More seasons and match statistics can be added later |
| Redundancy | Repeated descriptive information is separated from match events |
| Maintainability | Each table has a defined purpose |
| Filter propagation | Dimensions normally filter the central fact table |
| Model complexity | Most relationships remain straightforward one-to-many relationships |
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.
Top comments (0)