Data Modelling, Relationships and Joints in Power BI

Introduction

A Power BI report is only as reliable as the model beneath it. Data modelling is the process of organising tables, keys, relationships, and calculations so that business questions can be answered accurately and efficiently. A good model reduces ambiguity, makes DAX easier to write, improves query performance, and gives report users predictable filtering behaviour.
The attached Kenya Crops Data PBIX provides a useful starting point for this discussion. Its report definition shows a single table named Kenya_Crops_Dataset 5, with fields including County, Yield (Kg), Revenue (KES) and Profit (KES), plus measures named Total Revenue and Total Profits. In modelling terms, this is essentially a flat-table design: it can produce useful visuals quickly, but it also provides an excellent example of why a growing analytical solution may eventually benefit from separating facts and dimensions.

1. Data Modelling in Power BI

Data modelling is the process of deciding how the tables loaded into Power BI relate to one another, which tables exist, what each one contains, which columns act as keys, and how relationships connect them so that a filter selected in one visual correctly reaches every other value it should affect. It happens after data is loaded and cleaned in Power Query, and before any report visuals or DAX measures are built. It is the structural layer that everything else sits on.
A properly designed model supports reporting, analytics, DAX, performance, scalability and maintainability because each table has a clear purpose and each relationship reflects a real business rule.

Flat Table

A flat table stores every attribute and every measurable fact in one wide table, with one row per transaction or observation. The Kenya Crops Data model is an example of this approach: County, Yield, Revenue and Profit-related fields are available in the same dataset used by the report.

Advantages

• Simple to build and understand since there are no relationships to configure, so no risk of an incorrect join.
• Fast for very small datasets or fast analysis
• Every value is visible in one place, which is convenient for data exploration just as in excel.

Disadvantages

• Repeated descriptive values increase redundancy
• Changes to descriptive attributes become harder to manage
• A single wide table can make the semantic model less reusable
• No natural place for hierarchy or for attributes that don't change per transaction, such as a farmer's registration details

When it is appropriate

Flat tables are appropriate for small, static datasets, quick prototypes, or reports with a short shelf life where the overhead of proper modelling isn't needed. It becomes a liability once the dataset grows, needs multiple related fact sources.

Star Schema

A star schema places one central fact table, holding the measurable, numeric events (yield, revenue, profit), surrounded by multiple dimension tables, each holding descriptive attributes for one subject area (county, crop, date, farmer). Every dimension connects directly to the fact table, and the dimensions do not connect to each other. Viewed in Power BI's Model view, the fact table sits in the middle with lines radiating outward to each dimension, resembling a star.

The star schema is the default preferred approach for the overwhelming majority of Power BI reporting and analytics solutions, from small departmental dashboards to enterprise semantic models because it balances simplicity, performance, and maintainability better than either alternative.
It provides a simple filter path, clear table responsibilities, efficient columnar storage and straightforward DAX. Its main disadvantage is that it requires more modelling work than a flat table and may require surrogate keys or dimension-management processes.

Snowflake Schema

A snowflake schema normalises one or more dimensions into additional related tables. For example, Product could connect to Category, while Location could be separated into County, Region and Country tables.

Snowflaking is useful where dimensions are genuinely shared, large, hierarchical or governed centrally, but unnecessary normalisation is usually best avoided.

Advantages

• Removes duplicate attribute values
• Can better reflect a genuine multi-level business hierarchy as separate, independently maintainable tables.
• Slightly smaller storage footprint for very large, highly repetitive dimension attributes

Disadvantages

• More tables to manage, name, and document
• Extra hops mean filters must pass through an additional relationship to reach the fact table, which can slow queries and complicate DAX
• The storage savings are usually negligible in Power BI

Fact Tables and Dimensional Tables

The star and snowflake schemas both rest on a single distinction, separating what happened (facts) from who, what, when, and where it happened to (dimensions).

Fact Tables

A fact table stores the measurable, numeric business events such as sum, average, or count.
Fact tables are typically long with many rows and narrow, having few columns that are mostly foreign keys plus numeric measures.
The grain of a fact table is the precise definition of what a single row represents, the level of detail at which facts are recorded. For FactCropProduction, the grain might be "one row per farmer, per crop, per season, per county." Getting the grain right is one of the most important modelling decisions.
Every measure and every dimension key in the fact table must be true at that declared grain.

Dimension Tables

A dimension table stores descriptive, mostly textual attributes that give context to the facts, the who, what, when, and where. Dimension tables are typically short (relatively few distinct rows) and wide (many descriptive columns), and they change far less often than fact rows accumulate.

Worked Example: A Star Schema for Crop Production

Bringing this together, a central FactCropProduction table connects to four dimensions, each answering a different question about the same production event:
DimDateanswers "when?" — supporting month, season and year-over-year analysis.
DimCounty answers "where?" — supporting county- and region-level comparison.
DimCrop answers "what?" — supporting crop and crop-category breakdowns.
DimFarmer answers "who?" — supporting analysis by farm size or individual farmer.

Relationships in Power BI

A relationship is a logical link between two tables, defined by matching a column in one table to a column in another. It does not copy or merge any data, it simply tells Power BI's engine how two tables are connected through columns, so that a filter applied to one table can propagate to the other. Relationships are what make it possible to split data across multiple tables.

One-to-Many (1:*)

This is usually the standard dimensional relationship, where one row in DimProduct can correspond to many rows in FactSales. ProductID is unique on the dimension side but may repeat on the fact side. This should normally be the default pattern for Power BI models.

One-to-One (1:1)

Each row in one table relates to exactly one row in the other, and vice versa, both key columns contain entirely unique values.

Many-to-Many (:)

Both sides of the relationship can contain repeated (non-unique) values, where many rows in Table A can match many rows in Table B.

Keys, Uniqueness, Cardinality and Referential Integrity

Primary key: This the column (or columns) in a dimension table that uniquely identifies each row
Foreign key: The corresponding column in the fact table that references a dimension's primary key
Cardinality: The mathematical nature of the relationship (1:1, 1:, *:1, or *:), which Power BI detects automatically when a relationship is created but which should always be reviewed and confirmed by the modeller
Referential integrity: the assurance that every foreign key value in the fact table actually has a matching primary key value in the dimension
Active and inactive relationships: Power BI allows only one active relationship between two given tables at a time

Filter Direction

Once a relationship exists, Power BI needs to know which direction a filter is allowed to travel across it. This is the relationship's cross-filter direction, and it determines how selecting a value in one visual affects the values shown in another.

Single-Direction Filtering

In a single-direction relationship, filters flow only from the "one" side to the "many" side, from a dimension into the fact table. This is the default and recommended setting for the great majority of relationships in a star schema.

Bidirectional (Both) Filtering

A bidirectional relationship allows filters to flow both ways, a selection in the fact table can also filter the connected dimension table, and vice versa.

Why Bidirectional Filtering Needs Care

Bidirectional filtering is powerful but risky, and should be switched on deliberately, not by default:

• Ambiguous filter paths: if multiple bidirectional relationships create more than one route by which a filter could reach a table, Power BI may refuse to create the relationship, or worse, resolve the ambiguity in a way the model author didn't intend.
• Unnecessary model complexity: every bidirectional relationship is another path a report author (or a future you) has to mentally trace when a number looks wrong.
• Performance cost: bidirectional relationships require the engine to evaluate filters in both directions, which is more expensive than a single-direction filter, especially on large fact tables.
• Circular relationships: combining bidirectional filtering with certain model shapes e.g., a many-to-many bridge plus a bidirectional dimension can create circular filter logic that Power BI will block outright

Joins in Power Query

A join in Power Query is performed through Merge Queries. Unlike a model relationship, a merge combines columns from one query with matching rows from another query during data preparation. The result becomes part of the query output loaded into the model.

Left Outer Join

Keeps every row from the left (first) table, and adds matching columns from the right table where a match exists.
This is the default and most commonly used join in Power Query since it is appropriate whenever you want to enrich a complete list.

Right Outer Join

This join keeps every row from the right table, adding matching columns from the left table where available, and nulls where not.

Full Outer Join

Keeps every row from both tables, matching where possible and filling with nulls on whichever side has no match.

Inner Join

Keeps only rows where a match exists in both tables, and anything unmatched on either side is dropped entirely.
Inner joins are appropriate when incomplete or unmatched data is not useful for the analysis.

Left Anti Join

Keeps only rows from the left table that have no match in the right table, and the matching rows are excluded

Right Anti Join

The mirror of a left anti join, keeping only rows from the right table that have no match in the left table.

Power Query Joins vs Power BI relationships

Merging in Power Query and relating tables in the data model look similar since both connect two tables on a common key, but they operate at different stages of the Power BI workflow and have very different effects on the model.
A Power Query merge happens during data transformation before the final model is used for reporting by physically adding columns/rows to the query result.
A Power BI relationship is a semantic connection between already separate model tables, it does not copy the columns of one table into another.

Recommended Power BI Model

For a typical business intelligence project, I would recommend a star schema rather than a flat table or a heavily snowflaked model. The key reason is balance, as it provides enough separation to reduce redundancy and support scalability without introducing the relationship complexity of excessive normalisation.
A star schema normally gives better report readability, simpler DAX, predictable filter propagation and a strong foundation for adding new facts and dimensions. Power BI's columnar storage engine also benefits from well-structured tables with appropriate data types and low cardinality dimension attributes. The model remains easier to test because each relationship has a clear business meaning.
My default relationship design would be one-to-many from dimensions to facts, active relationships for the main analytical path, and single-direction filtering. Bidirectional relationships would be introduced only when there is a demonstrated business requirement and the resulting filter paths are unambiguous.
The final objective is not simply to minimise the number of tables. It is to create a model in which each table has one clear responsibility, each relationship represents a valid business rule, and users can build reports without needing to understand the physical complexity of the source system.

Conclusion

Data modelling decisions in Power BI are rarely about right or wrong in the abstract, they are about matching the shape of the model to the shape of the analysis it needs to support. A flat table is honest and simple but scales poorly. A star schema strikes the best balance for almost every practical reporting scenario, a snowflake schema earns its extra complexity only in specific, large-scale situations. Relationships, cardinality, filter direction, and Power Query joins are the mechanics that make any of these structures actually work, understanding when to use a one-to-many (1:*) relationship versus a merge, or when to allow bidirectional filtering versus keeping it single-direction, is what turns a technically correct model into a genuinely usable one. Relationships connect semantic tables without physically merging them, while Power Query joins reshape data before it reaches the model. Understanding this distinction allows a data scientist to build solutions that are accurate, performant, scalable and maintainable.