Power BI Data Modelling, Relationships & Joins

Introduction Data modelling involves organizing tables, defining relationships between them, determining how filters move through the model, and deciding how business data should be represented for analysis. In a typical business intelligence environment, data may come from databases, Excel files, APIs, enterprise systems, or cloud platforms. These sources often contain related information in separate tables. For example, a retail company may maintain customers in one table, products in another, transactions in a sales table, and calendar information in a date table. Power BI must understand how these tables are related before it can correctly answer questions such as: - Which products generated the most revenue? - How much did each customer spend? - Which region recorded the highest sales? - How did monthly revenue change over time? This article explores the major concepts involved in building such a model: flat tables, star schemas, snowflake schemas, fact and dimension tables, relationships, cardinality, filter direction, Power Query joins, and the difference between joins and model relationships. 1. Data Modelling in Power BI As defined above, data modelling in Power BI is the process of structuring data into logically related tables so that it can be efficiently analyzed. A good model makes it easier for Power BI to determine how information from different tables should interact. Microsoft's Power BI guidance recommends models in which dimension tables are generally used for filtering and grouping, while fact tables are used for summarization. Microsoft also recommends maintaining fact tables at a consistent grain. Why Data Modelling Matters Reporting Report developers can easily understand which fields should be placed in slicers, axes, tables, and measures. A clear model makes calculations easier to write. For example: Total Sales = SUM(FactSales[SalesAmount]) With a properly related DimProduct table, the same measure automatically works when the report is filtered by product, brand, or category. Performance Poorly designed models can contain unnecessary columns, repeated descriptive information, excessive relationships, or ambiguous filter paths. A simplified star-shaped model normally allows Power BI to evaluate queries more predictably. Scalability A model containing a million sales transactions should not need the customer's name, location, category, and other descriptive values repeatedly stored in every sales row. Dimensions allow descriptive data to be stored separately and reused. Maintainability Logical separation makes troubleshooting easier. If product descriptions change, developers know to investigate the product dimension instead of searching a massive sales table. 2. Flat Table Model A flat table stores most or all information in one table. For example: | OrderID | Date | Customer | County | Product | Category | Quantity | UnitPrice | SalesAmount | |---|---|---|---|---|---|---|---|---| | 1001 | 2026-09-01 | Amina | Nairobi | Laptop | Electronics | 1 | 85000 | 85000 | | 1002 | 2026-09-01 | Brian | Nakuru | Mouse | Accessories | 2 | 1500 | 3000 | | 1003 | 2026-09-02 | Amina | Nairobi | Keyboard | Accessories | 1 | 4500 | 4500 | The table combines transaction information with customer, location, product, and date attributes. There are no relationships because all the required information exists inside the same table. Advantages A flat table is simple to understand and can be appropriate for small datasets or quick exploratory analyses. It also avoids the need to create relationships because visualizations can reference columns directly from one table. Disadvantages The biggest problem is data repetition. If a customer makes 10,000 purchases, their name, county, and other customer details may appear 10,000 times. This creates: - unnecessary redundancy - larger models - more difficult maintenance - less intuitive organization - potential data quality problems - reduced scalability A flat table can also make it difficult to distinguish between descriptive attributes and measurable business events. A flat table can be useful when: - the dataset is small - there is only one analytical subject - a report is temporary - the source is already highly simplified - the model contains few columns - complex analytical relationships are unnecessary 3. Star Schema A star schema consists of a central fact table surrounded by dimension tables. The fact table contains measurable business events while dimensions contain information describing those events. The structure resembles a star, which gives the schema its name. Consider a retail sales model: flowchart TB CUSTOMER["DimCustomer CustomerID CustomerName Segment"] PRODUCT["DimProduct ProductID ProductName Category"] DATE["DimDate DateKey Date Month Year"] LOCATION["DimLocation LocationID County Region"] SALES["FactSales SaleID CustomerID ProductID DateKey LocationID Quantity SalesAmount"] CUSTOMER --> SALES PRODUCT --> SALES DATE --> SALES LOCATION --> SALES Advantages A star schema provides: - clear separation of facts and dimensions - simple relationships - simpler DAX - predictable filter propagation - improved model readability - easier reporting - good scalability - reduced duplication of descriptive attributes Disadvantages Creating a star schema normally requires some preparation. Source systems frequently do not provide perfectly structured fact and dimension tables, so developers may need to: - clean data - create surrogate keys - remove duplicates - build dimension tables - combine multiple operational sources A star schema can therefore require more initial modelling work than simply loading one flat spreadsheet. Appropriate Situations Star schemas are particularly suitable for: - sales analysis - financial reporting - inventory reporting - marketing analytics - customer analytics - operational dashboards - enterprise BI systems 4. Snowflake Schema A snowflake schema extends a star schema by further normalizing dimensions into related tables. For example, instead of keeping category information inside DimProduct , product categories may have their own table. flowchart LR CATEGORY["DimCategory CategoryID CategoryName"] PRODUCT["DimProduct ProductID ProductName CategoryID"] SALES["FactSales SaleID ProductID CustomerID DateKey SalesAmount"] CUSTOMER["DimCustomer"] DATE["DimDate"] CATEGORY --> PRODUCT PRODUCT --> SALES CUSTOMER --> SALES DATE --> SALES Advantages Snowflaking can: - reduce duplicated dimension attributes - represent complex organizational hierarchies - closely reflect normalized warehouse structures - allow some dimensions to be reused Disadvantages The model contains more tables and relationships. This can create: - more complex filter paths - a less intuitive Fields pane - more relationships to maintain - more work for report developers - potentially more complex queries Snowflake structures can make sense where dimensions contain substantial reusable hierarchies or where the Power BI model intentionally reflects an existing enterprise data warehouse. However, unnecessary snowflaking should generally be avoided where dimension tables can reasonably be flattened. 5. Comparing the Three Modelling Approaches | Characteristic | Flat Table | Star Schema | Snowflake Schema | |---|---|---|---| | Number of tables | Usually one | Several | Several/many | | Complexity | Low initially | Moderate | Higher | | Data redundancy | High | Low/moderate | Lowest | | Relationships | Few/none | Simple | More complex | | DAX usability | Acceptable for simple models | Excellent | Can be more complex | | Scalability | Limited | High | High | | Model readability | Declines as table grows | High | Moderate | | BI suitability | Small/simple analysis | Excellent | Specialized scenarios | | Maintenance | Difficult at scale | Good | More relationships to manage | For most Power BI business intelligence solutions, the star schema offers the best balance between performance, usability and maintainability. 6. Fact Tables and Dimension Tables The distinction between fact and dimension tables is fundamental to dimensional modelling. Fact Tables A fact table records business events. Examples include: - a sale - an order - a payment - a website visit - a bank transaction - an inventory movement Fact tables are normally the largest tables in analytical systems. 7. Dimension Tables Dimension tables contain descriptive information used to classify, filter, and group business events. Microsoft describes dimension tables as tables that support filtering and grouping, whereas fact tables support summarization. For example: Typical dimensions include: DimCustomer DimProduct DimDate DimLocation DimEmployee DimSupplier 8. Measures Versus Descriptive Attributes An easy way to understand the distinction is: DIMENSIONS describe: Who? What? Where? When? FACTS measure: How many? How much? How long? How often? For example: CustomerName ─┐ ProductName ─┼──► describe the sale County ─┤ Month ─┘ Quantity ─┐ Revenue ─┼──► measure the sale Profit ─┘ A report might therefore ask: What was Total Sales by Product Category in Nairobi during August 2026? In this question: - Total Sales = fact/measure - Product Category = dimension - Nairobi = dimension - August 2026 = dimension 9. Grain or Granularity The grain of a fact table defines what one row represents. This must be decided before designing the table. For example: One row in FactSales represents one product line on one customer transaction. Suppose order ORD1001 contains three products. The fact table would contain three rows: | OrderID | ProductID | Quantity | |---|---|---| | ORD1001 | P01 | 2 | | ORD1001 | P03 | 1 | | ORD1001 | P09 | 4 | The grain is therefore not one row per order but one row per product per order. Mixing different grains in the same fact table can produce incorrect aggregations. Microsoft specifically recommends that fact tables load data at a consistent grain. 10. Practical Star Schema Example Consider an electron