POWER BI: Data Modelling, Relationships & Joins

Building a good Power BI report is not just about visuals; anyone can drag a pie chart or a column bar chart onto a report. What really separates a working report from one that fails often lies beneath the visuals, and that is the data model. This article walks through the four pillars of Power BI data design: data modelling schemas, fact and dimension tables, relationships and filter direction, and joins in Power Query.

Data Modelling in Power BI

Data modelling is the process of organising the tables in your dataset, defining how they connect, and shaping them so that analysis becomes natural rather than forced. This usually happens in two places: Power Query, where raw data is cleaned and shaped before loading, and the Model view in Power BI Desktop, where tables are linked by relationships.

This matters for a few very practical reasons:
Reporting accuracy. A poorly structured model makes it easy to double count values or filter incorrectly without realising it.

DAX simplicity. Measures written against a clean model are short and easy to reason about. Measures written against a messy model often need extra logic just to compensate for the structure.

Performance. Power BI's engine compresses and stores data column by column. A model with fewer, narrower, well structured tables compresses better and queries faster than one wide table full of repeated text.

Scalability. A model that works with fifty thousand rows today needs to still work when it grows to five million rows next year. Structure is what allows that growth without a rebuild.

Maintainability. When a business rule changes (say, a product category gets renamed), you want to update it in one place, not chase it through thousands of duplicated rows.

Analysis. Clear relationships between tables mean you write simpler formulas and avoid messy, massive joins which make analysis of data simpler and faster.

I will compare three modelling approaches in this article:
Flat Table
Star Schema
Snowflake Schema

Flat Table

This is a single, wide table that holds everything, such as customer_id, product_id, date and price, etc., all side by side in one table with the rows repeating whatever information it belongs to.

Advantages

• It is simple to understand and build for a one-off report.
• There are no DAX calculations needed for basic slicing.
• No relationships to manage or configure.

Disadvantages

• Deteriorating performance as the table grows into maybe millions of rows.
• Massive repetition of customer names and city as they are repeated on every single line for that customer.
• Harder to maintain, especially if a detail changes, since it may need to be changed across thousands of rows.
• No clear home for shared logic, such as a calendar, and no natural way to handle facts of different grains.

This table is appropriate in the following scenarios: tiny datasets, quick proofs of concept, or single-purpose extracts. It is rarely appropriate for a real business intelligence solution.

A flat table looks simple but scales badly. Power BI's compression engine works best on narrow, repetitive columns, and a flat table full of repeated text values fights against that. There is also no natural way to build clean relationships later without going back and rebuilding the data.

Star Schema

The model places one central fact table in the middle, surrounded by dimension tables, each connected directly to the fact table. When sketched, it looks like a star, hence the name.

The table is structured in a way that each dimension holds descriptive attributes and its own primary key. Every dimension connects straight to the fact table with a one-to-many relationship.

Advantages

• Easy for business users to read and navigate.
• Very DAX friendly since filters travel in a short, predictable path.
• Scales well as new dimensions or facts slot in cleanly.
• Compresses and performs well, since dimensions hold the repeated text and the fact table holds mostly numbers and keys.

Disadvantages

• Requires some upfront design work to separate facts from dimensions correctly.
• Some duplication still exists inside dimension tables (for example, product name and category repeating per product).

When it is appropriate: This is the default recommended approach for the vast majority of Power BI projects, from small departmental reports to large enterprise models.

Star schemas generally align well with how Power BI's analytical engine works, making them a strong default for reporting models.

Snowflake Schema

This is a star schema taken one step further where the dimension tables are split further into smaller related tables. A product dimension might be broken into product, subcategory and category tables.

Structure. A fact table connected to dimension tables, some of which are further connected to smaller sub-dimension tables, forming a branching, snowflake-like shape.

Advantages

• Reduces duplication even further than a star schema, since shared attributes (like category names) live in one place.
• Can mirror a normalised source database closely, which is sometimes convenient during a migration
• Attributes are grouped logically, which can suit very large attribute sets

Disadvantages

• More tables and relationships to manage, hence a harder navigation
• More complex for report authors and also slightly heavier DAX in some cases
• The model view starts to look cluttered, which hurts readability for the next developer.
• Can hurt performance if overused, because Power BI has to resolve more relationship hops.

This type of table is appropriate when a dimension is large and shared across many other dimensions or when strict normalisation is a hard requirement.

Fact Tables and Dimension Tables

After committing to one schema (star or snowflake) everything now depends on correctly telling facts and dimensions apart.

Fact Table

These store measurable business events; they are the things that happened: sales, orders, and shipments. Facts are the verbs of the business: what happened, how much, and how many. These tables typically contain:
a) Foreign keys pointing to the relevant dimension tables (productid, customerid, etc.)
b) Measures which are numeric values you want to sum, average or count, such as cost, quantity or revenue.
They are usually long and narrow; they can have millions of rows but few columns.
An important concept is the grain, also referred to as granularity. This is what one row represents. It might be one row per order line, one row per individual transaction, or one row per day per product. Getting the grain wrong, or mixing grains in the same table, is one of the most common causes of confusing totals in a Power BI report. Every measure you write should be interpreted correctly at the grain the table was built at. Examples of fact tables include FactSales, FactOrders and FactTransactions.

Dimension Table

A dimension table stores the descriptive attributes that give the fact table its context and meaning. These are the who, what, where and when of the business event. Dimension tables typically contain:
a) A primary key that uniquely identifies each row (CustomerID, ProductID, DateKey).
b) Descriptive attributes, such as names, categories, regions and other text or classification fields that do not change with every transaction.
They are usually short and wide compared to fact tables and may have few rows but very many columns.
Common examples of dimension tables include DimCustomer, DimProduct, DimDate and DimLocation.

For example
Picture a retail business selling products to customers across different cities. Each sale is linked to a customer, a product, a date, and a store, while the Sales Fact table records the actual transaction. The surrounding dimension tables then provide the context needed to analyse those sales by customer, product, time, and location.

The image shows a retail star schema with the Sales Fact table at the centre. It connects to four dimension tables: Date Dimension, Customer Dimension, Store Dimension, and Product Dimension, which provide the details needed to understand each sale by customer, product, time, and location.

Relationships in Power BI

Real business data lives in separate tables. Sales may be in one system, customers in another and products in the third. A relationship in Power BI is a connection link between two tables based on a shared column, which tells Power BI to match rows in one table to rows in another table. Without relationships we would have to merge everything in a single table, which is exactly what we want to avoid. Also selecting a product category would have no effect on the sales number at all. The relationship is what makes filtering, aggregating, and cross-table DAX calculations possible in the first place.

Relationships are in cardinalities. Cardinality describes the shape of the relationship, specifically how many matching rows exist on each side.

One-to-Many (1:*). This is the most common relationship. One row on the dimension side matches many rows on the fact side. One customer places many orders; one product appears on many order lines. The primary key sits on one side, the foreign key on the other.

One-to-One (1:1). Each row on one side matches exactly one row on the other, typically because two tables describe the same entity, such as employee and person, country and country flag, or employee and employee parking pass. It is used sparingly, usually when a set of columns has been deliberately split into a second table for organisational or security reasons. If the two tables could easily be one, then they should be.

Many-to-Many (:). Many rows on one side can match many rows on the other. A student may enrol in many courses, and a course has many students. This is run well in Power BI but should be used carefully since it can lead to unexpectedly duplicated results if the underlying business logic is not well understood. It is safer to introduce a bridging table that breaks it into a one-to-many relationship instead.

Primary Keys, Foreign Keys and Related Concepts

Primary Key. A column (or set of columns) that uniquely identifies each row in a table such as CustomerID is the primary key of DimCustomer. It should uniquely identify each row and should not have blank values.
Foreign Key. A column in one table that references a primary key in another, such as a customerID, can appear in FactSales, but there it is a foreign key and is expected to repeat many times.
Unique Values. A primary key column must contain unique values for a relationship to behave predictably. This is exactly why CustomerID is unique in Dim Customer but appears repeatedly in FactSales.
Cardinality. As covered above, this defines the shape of the match between the two sides of a relationship.
Referential integrity. The assumption that every foreign key value actually exists on the primary side. Orphan keys (a sale pointing to a customer who is not in DimCustomer) cause blank rows or missing matches unless you clean them in Power Query.
Active and inactive relationships. A table pair can have more than one relationship, but only one can be active (the solid line). The others stay inactive (dashed lines) and are only used on demand with DAX functions such as USERELATIONSHIP.

Filter Direction

When a user clicks a slicer, Power BI does not move any data. It sends a filter through the relationship lines to decide which rows of the fact table take part in the calculation. Direction controls where that filter is allowed to go.

Single Direction Filtering

In a standard one-to-many relationship, filters flow from the "one" side to the "many" side by default. This means selecting a value in a dimension table filters the connected fact table, but not the other way around.

Bidirectional Filtering

Filters travel both ways. This enables features such as slicers cross-highlighting each other, and it can make visuals feel interactive. However, it should be used carefully for two main reasons: ambiguous filter paths, i.e., two competing routes from a dimension to a fact table, leaving the engine unsure which to use, which produces warnings and unreliable results. Unnecessary model complexity: Turning on bidirectional filtering everywhere, just in case, makes a model much harder to reason about and can quietly slow down report performance since Power BI now has more filter paths to evaluate for every single visual.

Always leave relationships as single-direction by default, and only switch a specific relationship to bidirectional when there is a clear, tested reason to do so.

Joins in Power Query

A join, inside Power Query, is a way of combining two tables based on matching values in a shared column, using the Merge Queries feature. Unlike a model relationship, a Power Query join actually produces a new, physically combined table as an output of the query.
To demonstrate each join type, imagine two simple tables:

Customers

CustomerID Name
1 Neal
2 Joe
3 Cruz

Orders

OrderID CustomerID Amount
101 1 500
102 1 250
103 4 800
Notice that CustomerID 4 in orders has no match in Customers, and CustomerID 3 (Cruz) in Customers has no matching order. This mismatch is exactly what makes the different join types behave differently.

Inner Join
Keeps only the rows where the join column has a match in both tables. Records retained: Only customers who have at least one matching order and only orders that have a matching customer. Example output: Neal's two orders (CustomerID 1) appear, matched with his name. Cruz and the CustomerID 4 order are both dropped, since neither has a match on the other side

Left Outer Join
Keeps every row from the first (left) table and attaches matching data from the second table where it exists. Records retained: All of the customers, plus matched order details where available. Example output: Neal appears twice (once per order), Joe appears with no order data attached, and Cruz appears with blank order fields, since she has no matching order.

Right Outer Join
The mirror image of a left join. Keeps every row from the second (right) table and attaches matching data from the first table where it exists. Records retained: All of the orders, plus matched customer details where available. Example output: All three order rows appear. The CustomerID 4 order shows up with blank customer fields, since no customer with that ID exists.

Full Outer Join
Keeps every row from both tables, matching them where possible and leaving blanks where there is no match on either side. Records retained: Everything from Customers and everything from Orders combined. Example output: Neal's two orders, Joe with no order, Cruz with no order, and the unmatched CustomerID 4 order, all appear in the same result.

Left Anti-Join
Keeps only the rows from the left table that have no match at all in the right table. Records retained: Customers with zero orders. Example output: Only Cruz appears, since she is the only customer with no matching order.

Right Anti-Join
The mirror image. Keeps only the rows from the right table that have no match in the left table. Records retained: Orders with no matching customer. Example output: Only the CustomerID 4 order appears, since that customer does not exist in the Customers table.
Anti-joins are useful for data quality checks, for example, finding orphaned records or verifying that referential integrity holds before loading data into a star schema.

Power Query Joins vs Power BI Relationships

Beginners often ask why they should bother with relationships when Merge Queries can combine tables. The two operations live at different stages of the workflow and solve different problems.

Merge physically combines tables. Relationships connect tables logically

A Power Query merge physically combines data. Columns from the second table are copied into the first, row by row. If the key repeats, rows multiply, and the result is one wider table loaded into the model. It happens during data preparation, before anything reaches the report.
A model relationship does not combine anything. Tables stay separate in memory, each holding its own data at its own grain. The relationship is a logical line the engine follows at query time to filter and aggregate across tables. It happens after loading, in the Model view.

A merge makes sense when you specifically need a flattened, denormalised table for a particular purpose, for example, preparing a lookup table for a specific export or doing a one-off data quality comparison.
However, merging tables together aggressively, as a habit, slowly turns a clean star schema back into something closer to a flat table. Every unnecessary merge reintroduces duplication, increases file size, and removes the very separation between facts and dimensions that made the model efficient and easy to filter in the first place. Keeping fact and dimension tables separate is preferable in a BI model because each table has one job, one grain, and one place to maintain each attribute. Relationships give you the analytical connection without the physical duplication.

Recommended Power BI Model

For the majority of real-world business intelligence projects, a star schema is the right default choice, with one-to-many relationships flowing from each dimension to the fact table and single-direction filtering used everywhere unless a specific, tested scenario genuinely requires otherwise.

The reasoning:
Query and report performance. Star schemas are what Power BI's VertiPaq engine is built to optimise. Short relationship paths mean faster filter resolution than either a flat table (which cannot filter at all in the same way) or a snowflake schema (which adds extra hops).
DAX simplicity. With single-direction relationships, measures like Total Sales, Sales YTD and Average Order Value stay short and readable. Ambiguity disappears, so there is nothing to debug.
Model readability. Anyone opening the model view can see, at a glance, what the fact table is and what each dimension describes. A flat table hides this entirely, and a snowflake schema spreads it across more tables than necessary.
Scalability. A star schema handles growth gracefully. Adding a new dimension, or millions more fact rows, does not require restructuring anything.
Model complexity. A star schema keeps the number of relationships and hops to a minimum, which keeps the whole model easier to test, document and hand over to someone else later.

A snowflake schema still has its place, particularly when a dimension is unusually large and shared across many other dimensions, or when a project has a hard requirement to mirror a normalised source system closely. A flat table can be acceptable for a genuinely small, one-off, throwaway piece of analysis. But as a general-purpose foundation for a Power BI solution meant to grow, get shared, and get trusted by a business, the star schema, built with one-to-many relationships and single-direction filtering as the default, remains the most balanced and dependable choice.

Conclusion

In Power BI, data modelling is more than just an intellectual exercise; it's what makes a report trustworthy or untrustworthy. Use Power Query merges just for true data preparation, link everything using one-to-many single-direction connections, keep the fact table short and honest about its grain, let dimensions represent the business, and let the star schema handle the hard lifting. If you do this, DAX becomes easier to write, performance becomes easier to manage, and the model can still make sense to the person who inherits it in two years.