Power BI: Data Modelling, Relationships & Joins

1. Data Modelling in Power BI

What is Data Modeling?
Data Modeling is how you organize and connect your tables in Power BI so they work together properly.

Data modelling is the discipline of not writing everything on one page. It's deciding where each piece of information belongs so it only must be written once, and everything else just points back to it.

In the Kenya Crops csv, "Kiambu" and "Potatoes" repeat on every row a Kiambu potato farmer shows up in. A well-designed model instead stores "Kiambu" once and "Potatoes" once. The harvest record in the fact table just holds a short reference (a key) back to each of them. That's the whole idea behind data modelling: separate the things that describe (farmer, county, crop i.e., who/what/where) from the things that happen (a harvest event with its yield, revenue, and cost), so each fact is stored once and reused everywhere it's needed.

If the data is all scrambled up, it may create problems when trying to find specific things. In Power BI, these problems include

1. Duplication
2. Difficulty in updating
3. Extra Large files

You can thus split a table into multiple smaller connected tables. These connections are called relationships which will be discussed later in this article.

Data modeling organizes data into a structured model by;

• Organizing tables and columns
• Defining relationships between tables
• Setting cardinality and cross-filter direction
• Supporting accurate calculations and reporting

Data Models

The underlying structure that organizes and relates data tables for analysis.

1. FLAT TABLE

Definition

A flat table is a data model in which most or all of the information required for analysis is stored in a single table. Instead of separating information into fact and dimension tables, descriptive attributes and numerical measures are stored together in the same table.

Structure

Using the Kenya crops dataset, a flat table could contain fields such as:
Crop | County | Sub-County | Year | Season | Area Harvested | Yield | Production
Every row represents a record, with all relevant information stored together.

Advantages

1. Simple to understand and create.
2. No relationships between tables are required.
3. Suitable for small datasets and simple reports.
4. Easy to use for basic Power BI visualizations.

Disadvantages

1. Information can be repeated many times.
2. Can become large and difficult to maintain.
3. Less suitable for complex analysis.
4. Limited scalability as the dataset grows.

Appropriate situations

A flat table is suitable for small datasets, simple reports, prototypes, or one-off analysis.

Power BI implications

It has low modelling complexity because there are few or no relationships. However, a large flat table can contain significant repetition and become less efficient as the dataset grows.

2. STAR SCHEMA

Definition

A mature data modeling approach with a central fact table surrounded by dimension tables, like points on a star, optimized for performance and simplicity in reporting.

Structure:

Center: Harvest (fact table with harvest transactions. one row per farmer's harvest, holding the numbers: Yield (Kg), Revenue (KES), Cost of Production (KES), Profit (KES))
Points: Farmer, Crop, County, Date (dimension tables, the who/what/where/when)
Connections: Each dimension connects directly to Harvest
Key characteristic: Dimensions are denormalized (flat)

Advantages

1. Easy to understand
2. Fast performance. Queries run faster. Refreshes complete quicker.
3. Easy to maintain
4. User friendly.
5. Easily scalable. Works the same for data with 1000 rows or 1 million rows
6. Simple measures

Total Yield = SUM('Kenya_Crops_Dataset 5'[Yield (Kg)])

Disadvantages

1. More complex than a flat table.
2. Requires relationships between tables.
3. Requires understanding of keys, relationships and data granularity.

Appropriate situations

A star schema is particularly suitable for medium-to-large Power BI reports, business intelligence systems, and datasets requiring multiple types of analysis.

Power BI implications

The star schema is generally the preferred approach for Power BI. Its clear separation between measurable data and descriptive data supports efficient filtering, grouping and calculations.

3. SNOWFLAKE SCHEMA

An extension of a star, and includes relationships between dimension tables and related sub-dimension tables. In short, Dimension tables have their own dimension tables, creating branches like a snowflake.

Structure

Center: Harvest (same fact table)
Points: Farmer, Crop, Date, and County but County now points on to a further table
Extra point: Region, connected only to County (not directly to Harvest)
Connections: Farmer, Crop, and Date connect directly to Harvest, exactly like the star. County also connects directly to Harvest, but County itself connects to Region one level out.

Advantages

1. Reduces duplication.
2. Organizes hierarchical information clearly.
3. Can be useful when dimensions contain complex hierarchies.
4. Can reflect a normalized database structure.

Disadvantages

1. Requires more tables and relationships.
2. More difficult to understand and maintain.
3. Creates longer filtering paths.
4. Can increase Power BI model complexity.
5. May be less efficient than a star schema for reporting.

Appropriate situations

A snowflake schema may be appropriate when the source data is already highly normalized or when complex hierarchies and reduced data redundancy are important.

Power BI implications

Snowflake schemas can work in Power BI, but the additional relationships can make the model more complex. For many Power BI reports, a star schema is easier to use and maintain.

2. Fact Tables and Dimension Tables

Every table in your model falls into one of these categories

Fact Tables: The “What Happened” Tables

Fact tables contain measurable business events; the transactions, the actions, the things you want to count or sum.

Characteristics:

• Mostly numbers and IDs (very little text)
• Many rows (thousands to millions)
• Each row is an event or transaction
• Grows over time (new rows added constantly)
• Contains the data you want to measure

For example, a sales fact table might contain:

• Sale ID
• Date ID
• Customer ID
• Product ID
• Location ID
• Quantity sold
• Sales amount
• Discount
• Profit

The numeric values represent measurable business events. For example, Quantity Sold can be added to determine total units sold, while Sales Amount can be summed to calculate total revenue.

Common examples include FactSales, FactOrders, FactTransactions, and FactProduction.

Fact tables normally contains numeric values (measures) and foreign keys that connect the facts to relevant dimension tables. The dimension key columns determine the dimensionality of a fact table, while the dimension key values determine the granularity of a fact table.

Grain or Granularity

The grain of a fact table describes exactly what one row represents. Defining the grain is important because it determines what can be accurately measured.

For example, consider a fact table designed to store sale targets that has two dimension key columns; Date and ProductKey. It's easy to understand that the table has two dimensions. The granularity, however, can't be determined without considering the dimension key values. In this example, consider that the values stored in the Date column are the first day of each month. In this case, the granularity is at month-product level.

Dimension Tables: The Who, What, When, Where Tables

Dimension tables contain descriptive information that gives context to your facts. They’re the details that help you slice, filter, and group your measures. The most consistent table you'll find in a star schema is a date dimension table.

A dimension table contains a key column (or columns) that acts as a unique identifier, and other columns. Other columns support filtering and grouping your data

Characteristics:

• Mostly text and categories
• Fewer rows (dozens to thousands)
• Relatively stable (changes infrequently)
• Contains the attributes you filter and group by
• Provides the “story” around the numbers

Examples include:

DimCustomer – Customer ID, customer name, gender, age group, city and customer segment.
DimProduct – Product ID, product name, category, brand and product type.
DimDate – Date ID, date, day, month, quarter and year.
DimLocation – Location ID, county, sub-county, region and country.

For example, a DimProduct table allows users to analyse sales by product, category or brand, while DimDate allows sales to be analysed by month, quarter or year.

How to Tell Them Apart

Ask yourself:

1. Does each row represent a transaction or event? Yes → Fact table No → Dimension table
2. Will this table grow significantly over time? Yes → Probably a fact table (new sales every day) No → Probably a dimension (your product list doesn’t double every week)
3. Am I measuring this or describing something? Measuring → Fact (measuring sales amounts) Describing → Dimension (describing what products are)
4. Does this table have mostly numbers or mostly text? Numbers → Fact Text → Dimension

A real scenario

You're building a report on Kenyan crop harvests. Here's the data:

1.Harvest records: 500 rows, growing every season as new harvests are logged. This is a fact table. Each row is a transaction, a farmer's harvest of a crop, with numeric outcomes like yield, revenue, cost, and profit.

2.Farmer list: grows slowly, but each farmer's name and contact only need to be stored once. This is a dimension table.

3.County list: around 12 counties, essentially fixed. This is a dimension table.

4.Crop catalog: crop type and crop variety, a short list that only changes when a new crop is introduced. This is a dimension table.

5.Seasonal price snapshot: if average market price per crop per county were captured at the start of every season, that's a recurring snapshot of a state at a point in time, similar to an inventory snapshot. It counts as a fact table, even though it's "just prices."

Table size doesn't decide fact vs dimension. DimFarmer could eventually hold more rows than a small fact table starts with, and it's still a dimension, because each farmer is described once. FactHarvest is a fact table because each row represents something that happened, not something being described.

The connection

Measures versus Descriptive Attributes

Fact tables contain numerical values or metrics used for summarization while Dimension tables contain descriptive attributes used for filtering or grouping

Fact tables sit at the center of the model. Dimensions surround them and provide the context to filter by.

Measure: Total Profit = SUM(FactHarvest[Profit (KES)])
Filter: County = "Kiambu" (from DimCounty)
Result: total profit earned by Kiambu farmers specifically

The relationship between FactHarvest and DimCounty is what makes that filter work. Without it, county would just be text sitting in the fact table with nothing to slice it by.

3. Relationships in Power BI

Relationships are the invisible bridges between your tables, letting Power BI treat them as connected without merging them into one table.
Without relationships, Power BI treats your tables as completely separate islands.

With proper relationships:

1. Filter by customer name → automatically filters their sales
2. Slice by product category → sees all products in that category
3. Your measures calculate correctly
4. Visuals respond to each other

In Excel, if you want to show customer names next to sales amounts, you use VLOOKUP. Then you copy that formula down 10,000 rows. If customer names change, your VLOOKUP might break. If you sort one column, the VLOOKUPs don’t update.

In Power BI, you create the relationship once. The relationship handles everything else. No copying formulas. No broken references when data update

Types of Relationships

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

One record on one side connects to many records on the other side. DimFarmer to FactHarvest is a good example: one farmer, many harvest records across seasons. The same applies to DimCounty and DimCrop connecting to FactHarvest. Use it whenever a dimension describes something the fact table records repeatedly.

2. One-to-one (1:1)

Each row on one side matches exactly one row on the other, with no repeats either way. This is uncommon in practice. An example here would be if Farmer Contact were split into its own table, with exactly one contact record per farmer, that would be 1:1. Usually it's a sign the two tables should just be combined instead.

3. Many-to-many (:)

Rows on either side can match multiple rows on the other. An example from this dataset would be Pest Control method versus Crop Type, where a crop can use several methods, and a method applies to several crops. This should be used carefully, since it can cause ambiguous filtering, and is often better resolved with a bridge table.

Keys:

• Primary key: uniquely identifies each row in a dimension table, for example FarmerID in DimFarmer, appearing once.

• Foreign key: the same column shown in the fact table, repeating once per event, for example FarmerID in FactHarvest appearing many times.

• Cardinality: the relationship type, whether 1:*, 1:1, or :. tells you how many records on each side can match.

• Referential integrity: every foreign key in the fact table must have a matching primary key in the dimension table, otherwise that row gets excluded from analysis.

• Active and inactive relationships: Power BI only allows one active relationship between two tables at a time. If FactHarvest had both a Planting Date and a Harvest Date needing to relate to DimDate, only one would stay active by default, and the other would need USERELATIONSHIP() in DAX to be used.

4. Filter Direction In Power BI

What It Means

Controls how filters travel between tables.

"Both" lets filters flow both ways;
"Single" (or "One direction") only lets filters flow from the one-side (usually a dimension) to the many-side (usually a fact). 99% of the time you should have single direction filters.

Affects which visuals and slicers interact.

Bidirectional can make slicers reduce available values across unrelated tables, but it can also create ambiguous filter paths and wrong results. It's generally considered a bad practice as bi-directional relationships can create ambiguity between tables which can lead DAX measures to return false results.

Used only for special cases like many-to-many or bridging.

Some scenarios (bridge tables, many-to-many) require BOTH to get the expected slicer behavior, but it increases complexity and risk.

Why It Matters

Affects model correctness and predictability. Too many bidirectional filters create ambiguous paths and unpredictable results as the engine decides how to apply filters. Too many bidirectional filters and PBI will decide how to apply these filters, this might lead to un-predictable results.

Impacts performance and maintenance. Using measures or virtual filters instead of physical bidirectional relationships can work but adds complexity and may hurt performance.
There are DAX workarounds when BOTH is undesirable. Techniques like ISEMPTY, TREATAS, or measure-level logic can simulate slicer-to-slicer behavior without enabling bidirectional everywhere.

Practical Guidance

Default to single direction for star schemas. Keep dimensions filtering facts; avoid BOTH unless needed.

When to use Both:

• Row-level security requirements
• Specific many-to-many scenarios with bridge tables
• You have a clear business reason and understand the impact
• Use BOTH for true many-to-many or bridge-table patterns. When a bridge or many-to-many scenario requires slicers to cascade across facts, BOTH is acceptable but document and test carefully.

Why to avoid Both (Bi-Directional):

• Slower performance
• Unexpected filtering behavior that confuses users
• Higher risk of circular dependencies
• Harder to debug when things go wrong #####The rule: Always start with Single. Only change to Both if you have a specific, documented reason.

Prefer explicit DAX if you need controlled behavior. Use measures that check activity (ISEMPTY) or TREATAS to make filter logic explicit and avoid ambiguous model paths.

6.Joins in Power Query

In Power Query, joins combine two tables based on matching values in one or more columns. When the compared row values between two tables are identical, they are considered a match. The behavior that follows depends on the join type.

Merge Queries in Power Query

The easiest way to join tables in Power Query is by using merge queries.
To start, go to the Home tab in the ribbon and navigate to the Combine section. There you will find the Merge Queries button. There are two options as below:

Option 1: Merge Queries performs a join on your current table and adds the result of this merge in the same query. This means you can easily see the impact of the merge in a single place.
Option 2: Merge Queries as New performs the same join on your table but puts the result of the merge in a separate query. The benefit of this approach is that your starting query remains the same. This means that you can reference that starting query for other purposes.

JOIN TYPES

a) Left Outer Join

The most common join. It returns all rows from the left table. Then, by matching one or more column values with rows from the left table with similar columns from rows in the right table, the join returns the matching values of the right table.
The Left Outer Join is often used when combining information from different tables that have a unique key to match. Power Query uses JoinKind.LeftOuter

b) Right Outer Join

The Right Outer Join returns all values from the Right Table while only returning the matching values from the Left Table. Its behavior is very similar to the Left Outer join, except that its base table is the Right table instead of the Left. It uses JoinKind.RightOuter

c) Full Outer Join

The Full Outer Join tries to match column values from the left table with the right table and if successful, puts them next to each other. The values of the left table that don’t exist in the right table get their own row. Any rows without a match in the right table return null values for these rows. Similarly, the values of the right table that don’t exist in the left table also get their own row. For these rows, the missing columns from the left table get null values. To perform a Full Outer Join, you can make use of the join type JoinKind.FullOuter

d) Left Anti Join

The Left Anti Join only returns rows from the Left Table. Based on the key columns, the join searches for which values in the left table do not exist in the right table. It then only returns the unique values from the left table. The Left Anti Join is a great way to perform checks on your queries. You can instruct Power Query a Left Anti Join by using JoinKind.LeftAnti

e) Right Anti Join

The Right Anti-Join is the twin sister of the Left Anti-Join. It returns the rows from the Right Table which are not present in the Left Table. To do that, it uses JoinKind.RightAnti

When to use Left Anti Join or Right Anti Join

The choice of which of the two joins to perform depends on where in your queries you want to see the result. If you quickly want to compare which values of another table are not present in the current table, the Right Anti Join lets you do it right away.
Yet if you want to see which values of the current table miss in another, the Left Anti Join lets you hit the ground running.

f) Inner Join

The Inner Join is a useful join that returns only those rows that have matching values in both the Left Table and the Right Table. This can be useful when creating tables for your data model.

6. Power Query Joins vs Power BI Relationships

Joins

In Power BI, you can use joins to physically combine tables based on common columns. This approach is ideal for smaller datasets or when specific aggregations on combined data are required.

Pros of Using Joins:

• Simplicity: Joins are straightforward to implement, especially for users with a SQL background.
• Aggregations: Enables direct aggregations on denormalized data for certain analysis needs.

Cons of Using Joins:

• Redundancy: Joins can lead to data redundancy and increased memory usage.
• Limited Flexibility: May not be suitable for scenarios requiring a more normalized data model.

Power BI Relationships:

Power BI’s relationships offer a more sophisticated approach, allowing you to establish logical links between tables without physically combining them. This is particularly beneficial for larger datasets or when maintaining a normalized data model is essential.

Pros of Using Relationships:

• Data Integrity: Relationships help maintain data integrity by avoiding unnecessary duplication.
• Flexibility: Provides a more flexible data model, allowing for better scalability and adaptability.
• Performance: Generally, relationships perform better with larger datasets.

Cons of Using Relationships:

• Complexity: Setting up relationships might be more complex, especially for those new to Power BI.

• Learning Curve: Understanding the nuances of relationships requires a learning curve for some users.

  Choosing the Right Approach:

• Consider Data Volume: For large datasets, relationships are generally more efficient in terms of performance and storage.

• Normalization Matters: If your goal is to maintain a normalized data model for better integrity and flexibility, relationships are the preferred choice.

• User Experience: Think about how end users will interact with the data. Relationships provide a seamless experience for drilling down into related data.

• Analysis Requirements: If your analysis involves aggregations and calculations that necessitate a denormalized dataset, joining tables might be more suitable.

Therefore, In the dynamic landscape of Power BI, choosing between joins and relationships is a pivotal decision that shapes the efficiency and effectiveness of your data modeling.

Whether you opt for the simplicity of joins or the sophistication of relationships depends on the specific needs of your project.

By understanding the strengths and weaknesses of each approach, you can elevate your Power BI skills and create compelling, insightful reports.

7. Recommended Power BI Model

For most Power BI analytical and business intelligence projects, a Star Schema is generally recommended. It provides a clear separation between fact tables, which contain measurable business events, and dimension tables, which contain descriptive information.

A typical Power BI model may include:

• FactSales – contains sales transactions, quantities, revenue, costs and other measurable values.
• DimCustomer – contains customer information such as customer name, location and customer category.
• DimProduct – contains product information such as product name, category, brand and product type.
• DimDate – contains dates, months, quarters and years.
• DimLocation – contains geographical information such as city, region and country.

The fact table is connected to the dimension tables through relationships, typically using a one-to-many relationship from each dimension to the fact table.

The star schema is recommended because it provides a good balance between performance, simplicity, scalability and ease of use. It also makes it easier to create Power BI reports, write DAX measures and allow users to filter and analyse business data.

A flat table may be suitable for small and simple reports, while a snowflake schema may be appropriate when highly normalized data structures or complex hierarchies are required. However, for most Power BI reporting scenarios, the star schema provides a more practical and user-friendly model.