Power BI Data Modelling, Relationships and Joins: A Practical Guide
When I began learning Power BI, I naturally focused on dashboards. However, an attractive dashboard can still be slow or misleading when the data behind it is poorly organised. The data model is the foundation: it determines how tables communicate, how filters move and how DAX measures behave.
This article uses a retail-sales example to explain modelling schemas, fact and dimension tables, relationships, filter direction and Power Query joins. It also addresses a common beginner question: if both joins and relationships connect tables, when should each be used?
1. Data modelling in Power BI
Data modelling is the process of organising data into tables and defining logical connections between them. Sales transactions may be stored separately from customers, products, dates and locations. A good model allows a user to select Electronics, Nairobi or 2026 and receive the correct result.
A well-designed model improves accuracy, makes DAX easier to write, reduces duplicated data, improves VertiPaq compression and supports future growth. It also makes the solution easier for another analyst to understand and maintain.
2. Flat table, star schema and snowflake schema
A. Flat table
A flat table stores facts and descriptions together. One Sales table might contain OrderID, Date, CustomerName, ProductName, Category, City, Quantity and SalesAmount.
Advantages: It is quick to build, familiar to Excel users and suitable for a small prototype. No relationships need to be managed.
Disadvantages: Product and customer descriptions repeat for every transaction, increasing table width and storage. Repetition creates inconsistent values and difficult maintenance. Large flat tables are less reusable and can make complex DAX harder to organise.
Best use: Small, one-purpose datasets with limited growth.
B. Star schema
A star schema places a fact table at the centre and connects it directly to dimensions such as Customer, Product, Date and Location.
Advantages: It provides clear filter paths, simpler DAX, less duplication and strong performance. Dimensions can also be reused by several reports.
Disadvantages: It requires deliberate preparation and a clearly defined fact-table grain. Dimensions may contain controlled repetition, such as Category repeated for products in that category.
Best use: Most production Power BI models, especially when data volume and reporting needs will grow.
C. Snowflake schema
A snowflake schema normalises dimensions. Instead of storing Category and Subcategory in DimProduct, it may use DimProduct → DimSubcategory → DimCategory.
Advantages: It reduces repetition within dimensions and can mirror a normalised source database or independently managed hierarchy.
Disadvantages: Extra tables and relationships increase model complexity. Filters cross more paths, fields are harder to find and DAX becomes less intuitive.
Best use: Large or independently governed hierarchies. For ordinary reporting, small snowflaked dimensions are often flattened in Power Query.
| Feature | Flat table | Star schema | Snowflake schema |
|---|---|---|---|
| Structure | One wide table | Central fact with dimensions | Dimensions split further |
| Redundancy | High | Low and controlled | Lowest |
| Performance at scale | Often weak | Usually strongest | More traversal required |
| Complexity | Simple initially | Clear and balanced | Highest |
3. Fact tables, dimension tables and grain
A fact table records measurable business events—what happened and how much. FactSales may contain DateKey, CustomerID, ProductID, LocationID, OrderID, Quantity, Cost and SalesAmount. It is usually tall and narrow because transactions grow continuously.
A dimension table provides context used for filtering, grouping and labelling. DimProduct may contain ProductName, Brand and Category; DimCustomer may contain CustomerName and Segment; DimDate may contain Month, Quarter and Year. Dimensions answer who, what, when and where, while facts answer how many and how much.
The grain states exactly what one fact row represents. If FactSales has a grain of one product line per order, an order containing three products creates three rows. Grain must remain consistent. Combining order totals with order-line records can double-count revenue.
| Table | Typical contents |
|---|---|
| FactSales | Foreign keys, Quantity, Discount, Cost, SalesAmount |
| FactOrders | One row per order and order-level measures |
| FactTransactions | Debit, Credit, Amount and account keys |
| DimCustomer | Customer name, segment, city |
| DimProduct | Product name, brand, category |
| DimDate | Date, month, quarter, year |
| DimLocation | Branch, city, region, country |
For example, Total Sales = SUM(FactSales[SalesAmount]) can be analysed by any related dimension without copying its descriptive columns into FactSales.
4. Relationships in Power BI
A relationship is a logical connection between columns in two loaded tables. It allows filters and calculations to work across tables without physically combining them.
A primary key uniquely identifies a dimension row. DimCustomer[CustomerID] should contain C001 once.
A foreign key is the matching fact column; FactSales[CustomerID] may contain C001 many times because that customer can purchase repeatedly. Related columns need compatible data types.
Referential integrity means each foreign key has a matching dimension key. If FactSales contains C999 but DimCustomer does not, Power BI may group the transaction under a blank member. Anti joins can help detect such orphan records.
One-to-many (1:*)
One unique dimension value matches many fact rows. One product appears once in DimProduct but can appear in thousands of sales. This is the standard star-schema relationship.One-to-one (1:1)
Each key occurs once in both tables—for example, one Employee row matched to one EmployeeSecurity row. It can separate sensitive fields, although merging the tables may be simpler when both describe the same entity at the same grain.Many-to-many (:)
Keys repeat on both sides. Many students take many courses. A direct many-to-many relationship can make totals difficult to interpret, so a bridge table containing unique StudentID–CourseID pairs is often safer. It connects to Students and Courses through two one-to-many relationships.
An active relationship appears as a solid line and operates automatically. An inactive relationship is dotted and must be activated in a measure. If FactSales has OrderDateKey and ShipDateKey linked to DimDate, Order Date may be active while Ship Date is inactive:
Sales by Ship Date =
CALCULATE(
[Total Sales],
USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey])
)
5. Filter direction
With single-direction filtering, a filter normally travels from the one-side dimension to the many-side fact. Selecting Electronics in DimProduct identifies the relevant ProductIDs, filters FactSales and recalculates Total Sales. This behaviour is predictable and is the recommended default.
Bidirectional filtering allows filters to travel both ways. It can help in carefully designed bridge-table scenarios, but it should not be enabled simply to make a visual work. Multiple routes between tables can create ambiguous filter paths, unexpected totals, more query work and difficult debugging.
6. Joins in Power Query
A join matches rows from two tables using a common column. In Power Query, select Home → Merge Queries, choose both tables and matching columns, choose a join kind then expand the required columns.
Customers (left)
| CustomerID | Name |
|---|---|
| C1 | Amina |
| C2 | Brian |
| C3 | Carol |
Orders (right)
| OrderID | CustomerID | Amount |
|---|---|---|
| O10 | C1 | 500 |
| O11 | C1 | 300 |
| O12 | C2 | 700 |
| O13 | C4 | 200 |
C3 has no order, while O13 refers to a missing customer. This reveals how each join treats unmatched records.
Left outer join
Keeps every customer and matching orders. Amina appears twice; Carol remains with null order values.
| CustomerID | Name | OrderID | Amount |
|---|---|---|---|
| C1 | Amina | O10 | 500 |
| C1 | Amina | O11 | 300 |
| C2 | Brian | O12 | 700 |
| C3 | Carol | null | null |
Use it when Customers is the master list and no customer should disappear.
Right outer join
Keeps every order and matching customers. O13 remains with a null customer name.
| CustomerID | Name | OrderID | Amount |
|---|---|---|---|
| C1 | Amina | O10 | 500 |
| C1 | Amina | O11 | 300 |
| C2 | Brian | O12 | 700 |
| C4 | null | O13 | 200 |
Use it when every transaction must remain. Reversing the tables and using Left Outer gives the same logic.
Full outer join
Keeps all matches and unmatched rows from both tables. The output contains the three valid order matches, C3 with null order fields and O13/C4 with a null name. It is useful for reconciliation, although it may create many nulls.
Inner join
Keeps only keys found in both tables: C1–O10, C1–O11 and C2–O12. C3 and O13 are removed. Use it when analysis requires valid matches only, but check row counts because unmatched data is silently excluded.
Left anti join
Keeps only left-table rows without a right-table match. The result is C3–Carol. It can identify customers who have never purchased, missing submissions or unused master records.
Right anti join
Keeps only right-table rows without a left-table match. The result is O13–C4–200. It is valuable for identifying orphan transactions or broken foreign keys.
7. Power Query merge versus model relationship
| Question | Power Query merge | Model relationship |
|---|---|---|
| Stage | Data preparation before loading | Model view after loading |
| Effect | Physically adds matching data to a query | Logically connects separate tables |
| Purpose | Enrichment, consolidation, reconciliation | Filtering and analysis |
| Structure | Can create a wider table | Preserves facts and dimensions |
Suppose FactSales has one million rows and DimProduct contains ProductName and Category. Merging repeats those descriptions across many transactions. A relationship stores each product once and uses ProductID to filter FactSales. This is generally cleaner and more efficient.
A merge is appropriate when creating a dimension from several sources, adding a small lookup required before loading, consolidating datasets or using anti joins for quality checks. Excessive merging can recreate a wide flat table and weaken the model.
8. Recommended model
For a typical BI project, I would use a star schema. I would define the grain first, create dimensions with unique keys and keep transactional measures in fact tables. Relationships would normally be one-to-many with single-direction filtering from dimensions to facts.
I would use inactive relationships for alternative dates, bridge tables for genuine many-to-many scenarios and bidirectional filtering only when the business requirement and path are clear. Power Query merges would prepare data rather than flatten the entire model.
This design improves compression, query performance, DAX simplicity, readability, scalability and maintenance. Most importantly, every table has a clear responsibility. Dashboards are what users see but a trustworthy model is what makes their answers correct.
Top comments (1)
Great explanation!