Building a robust Power BI solution starts long before the first visual is placed on a report canvas. The foundation of every performant, scalable, and maintainable Power BI report is its data model. This article explains the core concepts of data modelling in Power BI—schemas, fact and dimension tables, relationships, filter direction, and joins—and shows how to apply them when designing a real-world solution. 1. Data Modelling in Power BI Data modelling in Power BI is the process of organizing your data into tables, defining how those tables relate to one another, and structuring them so that reports, DAX calculations, and queries perform efficiently. A well-designed data model: Enables accurate and consistent reporting across multiple visuals and pages. Simplifies DAX by reducing the need for complex workarounds. Improves query performance and reduces memory usage. Scales as data volumes and business requirements grow. Makes the model easier to understand, maintain, and extend. Power BI supports three common schema types: flat (fully denormalized), star, and snowflake. 1.1 Flat Table (Fully Denormalized) Definition: A flat table schema stores all attributes—facts and dimensions—in a single, wide table. There are no relationships because there is only one table. Structure: Every row contains both transactional data (e.g., sales amount, quantity) and descriptive attributes (e.g., customer name, product category, region). Advantages: Simple to understand for beginners. No relationships to manage. Quick to build for very small, one-off analyses. Disadvantages: High data redundancy (same customer, product, or date repeated many times). Larger model size and slower refresh. Harder to maintain when attributes change (e.g., customer address updates). DAX can become verbose when implementing time intelligence or complex filters. When appropriate: Small datasets, quick prototypes, or when source data is already a single denormalized extract and performance is not a concern. Implications for Power BI: Flat models often lead to larger in-memory models and can degrade performance as data grows. They also make it harder to reuse dimension logic across multiple fact tables. Illustration (Flat Table): +---------------------------------------------------------------------------+ | FactSales_Flat | +---------------------------------------------------------------------------+ | SaleID | Date | CustomerID | CustomerName | ProductID | ProductName | |--------|------------|------------|--------------|-----------|-------------| | 1001 | 2025-01-01 | C001 | Alice | P100 | Laptop | | 1002 | 2025-01-01 | C002 | Bob | P101 | Mouse | | 1003 | 2025-01-02 | C001 | Alice | P102 | Keyboard | +---------------------------------------------------------------------------+ | Quantity | Amount | Region | Category | ... (many more columns) | |----------|--------|--------|----------|----------------------------------| | 1 | 1200 | Nairobi| IT | ... | | 2 | 50 | Mombasa| IT | ... | | 1 | 80 | Nairobi| IT | ... | +---------------------------------------------------------------------------+ 1.2 Star Schema Definition: A star schema consists of one central fact table surrounded by multiple dimension tables. Each dimension connects directly to the fact table via a one-to-many relationship. Structure: Fact table: Contains measurable business events (e.g., sales, orders). Dimension tables: Contain descriptive attributes used for slicing and dicing (e.g., customer, product, date). Advantages: Optimized for Power BI's VertiPaq engine and DAX. Clear separation of facts and dimensions improves readability. Efficient filter propagation from dimensions to facts. Easier to extend with new dimensions or facts. Disadvantages: Requires more upfront design than a flat table. Multiple tables mean relationships must be managed correctly. When appropriate: Most business intelligence scenarios, especially when you have clear business processes (sales, orders, transactions) and descriptive dimensions. Implications for Power BI: Star schemas are the recommended default in Power BI. They balance performance, simplicity, and scalability. Illustration (Star Schema): DimDate | | 1:* v DimCustomer <--+--> FactSales <--+--> DimProduct 1:* | 1:* | 1:* | | v v DimLocation DimCategory Each arrow represents a one-to-many relationship from the dimension (1) to the fact (*). 1.3 Snowflake Schema Definition: A snowflake schema is a normalized version of a star schema where some dimension tables are further split into related sub-dimensions. Structure: Dimensions are normalized into multiple related tables. For example, DimProduct might link to DimCategory, which links to DimSubCategory. Advantages: Reduces data redundancy in dimensions. Useful when dimensions are large and highly normalized in the source system. Disadvantages: More complex model with more relationships to manage. Can slightly degrade query performance due to extra joins. DAX can become more complex when navigating multiple dimension layers. When appropriate: When dimensions are very large, highly normalized, or shared across multiple fact tables in a way that justifies normalization. Implications for Power BI: Snowflake schemas can work well but often add unnecessary complexity in Power BI, where denormalized dimensions are usually more efficient. Illustration (Snowflake Schema): DimDate | | 1:* v DimCustomer <--+--> FactSales <--+--> DimProduct 1:* | 1:* | 1:* | | v v DimLocation DimCategory | | 1:* v DimSubCategory 2. Fact Tables and Dimension Tables Understanding the difference between fact and dimension tables is central to designing a good star schema. 2.1 Fact Tables What they store: Fact tables store measurable business events or transactions. Typical columns include: Foreign keys to dimension tables (e.g., CustomerID, ProductID, DateID). Numeric measures (e.g., Quantity, SalesAmount, Cost, Profit). Characteristics: Usually the largest table in the model. One row represents a single occurrence of a business event at a specific grain. Grain (granularity) defines what each row represents (e.g., one line item on an invoice, one order, one daily snapshot). Examples: FactSales: One row per sales transaction line. FactOrders: One row per order or order line. FactTransactions: One row per financial transaction. 2.2 Dimension Tables What they store: Dimension tables store descriptive attributes used to slice, filter, and group facts. Typical columns include: A unique key (e.g., CustomerID, ProductID). Descriptive attributes (e.g., CustomerName, City, ProductCategory, Date, Month, Year). Characteristics: Usually smaller than fact tables. Keys are unique within the dimension. Attributes are often textual or categorical. Examples: DimCustomer: CustomerID, CustomerName, Segment, Region. DimProduct: ProductID, ProductName, Category, Brand. DimDate: DateID, Date, Day, Month, Quarter, Year. DimLocation: LocationID, City, Region, Country. 2.3 Practical Example: Star Schema for Sales Consider a retail business tracking sales. The central fact table is FactSales, with dimensions for customer, product, date, and location. FactSales (grain: one row per sales line item): FactSales --------- SaleID (PK) DateID (FK) CustomerID (FK) ProductID (FK) LocationID (FK) Quantity SalesAmount Cost Dimensions: DimCustomer ----------- CustomerID (PK) CustomerName Segment Region DimProduct ---------- ProductID (PK) ProductName Category Brand DimDate ------- DateID (PK) Date Day Month Quarter Year DimLocation ----------- LocationID (PK) City Region Country Star schema diagram: DimDate | | 1:* v DimCustomer <--+--> FactSales <--+--> DimProduct 1:* | 1:* | 1:* | | v v DimLocation (other dims as needed) Each dimension filters FactSales through a one-to-many relationship, enabling intuitive slicing (e.g., sales by customer, by product, by month). 3. Relationships in Power BI A relationship in Power BI defines how two tables are connected via key columns, enabling filter propagation and accurate aggregations across tables. Without relationships, Power BI cannot correctly combine data from multiple tables in visuals or DAX. 3.1 Relationship Cardinalities Power BI supports three main cardinalities: one-to-many (1:), one-to-one (1:1), and many-to-many (:*). 3.1.1 One-to-Many (1:*) How it works: One row in the "one" table (usually a dimension) relates to many rows in the "many" table (usually a fact). Example: DimCustomer[CustomerID] (unique) → FactSales[CustomerID] (repeated). When to use: This is the most common relationship in BI models, connecting dimensions to facts. When not to use: Avoid using 1:* where many-to-many logic is actually required (e.g., students and courses via an enrollment bridge). Illustration: DimCustomer FactSales ------------ --------- CustomerID (PK) ----1:*---> CustomerID (FK) CustomerName SaleID Segment Quantity SalesAmount 3.1.2 One-to-One (1:1) How it works: Each row in Table A matches at most one row in Table B, and vice versa. Example: DimEmployee and DimEmployeeDetails, where each employee has exactly one detail record. When to use: Rarely needed in Power BI. Often indicates that two tables should be merged into one. When not to use: If the relationship is truly 1:1 and within the same source, it's usually better to merge the tables in Power Query to simplify the model. Illustration: DimEmployee DimEmployeeDetails ----------- ------------------ EmployeeID (PK) 1:1 EmployeeID (PK) Name HireDate Department SalaryGrade 3.1.3 Many-to-Many (:) How it works: Multiple rows in Table A can relate to multiple rows in Table B. Example: Students and Courses via an Enrollment bridge table, or products and promotions where a product can have many promotions and a promotion can apply to many products. When to use: When there is no direct 1:* path and a bridge table is required to correctly model the business logic. When not to use: Avoid many-to-many between fact tables. Instead, relate each fact to shared dimensions in a star schema. Illustration (via bridge): DimProduct 1:* BridgeProductPromo :* DimPromotion Power BI guidance recommends implementing many-to-many via a bridge table with two 1:* relationships, rather than a direct : relationship where possible. 3.2 Keys, Uniqueness, Cardinality, and Referential Integrity Primary Key (PK): A column (or set of columns) that uniquely identifies each row in a table (e.g., CustomerID in DimCustomer). Foreign Key (FK): A column in one table that references a primary key in another (e.g., CustomerID in FactSales). Unique values: Dimension keys should be unique; fact table keys can repeat. Cardinality: Describes the numeric relationship between rows in related tables (1:1, 1:, *:). Referential integrity: Ensures that every foreign key value in the fact table has a matching primary key in the dimension. Violations (orphaned keys) can cause incorrect results or BLANKs in visuals. Active vs inactive relationships: Only one active path can exist between two tables for filter propagation. Additional relationships can be defined as inactive and used in DAX via USERELATIONSHIP. Example: DimCustomer[CustomerID] is unique (each customer appears once). In FactSales, CustomerID appears many times because a customer can make many purchases. This is a classic 1:* relationship. 4. Filter Direction Filters in Power BI propagate along relationship paths. Understanding filter direction is critical for predictable behavior. 4.1 Single-Direction Filtering By default, relationships filter from the "one" side to the "many" side. Example: Selecting "Laptop" in DimProduct[ProductName] filters FactSales to only rows where ProductID matches "Laptop". Flow: DimProduct (1) --filters--> FactSales (*) This is the recommended default because it is simple and avoids ambiguity. 4.2 Bidirectional (Both) Filtering Bidirectional filtering allows filters to flow in both directions across a relationship. Example: With bidirectional filtering between DimProduct and FactSales, selecting a product filters sales, and selecting a sales record (e.g., via a visual filter) can also filter the product dimension. When to use carefully: Bidirectional filtering can: Create ambiguous filter paths when multiple routes exist between tables. Increase model complexity and make behavior harder to predict. Degrade performance in large models. Best practice: Use single-direction filtering by default. Enable bidirectional filtering only when there is a clear need and after testing for ambiguity and performance. 5. Joins in Power Query In Power Query, joins are performed using Merge Queries. A merge combines columns from two queries based on matching key columns. This happens during the data transformation stage, before the data is loaded into the model. Consider two tables: Customers: Customers --------- CustomerID | CustomerName | Region C001 | Alice | Nairobi C002 | Bob | Mombasa C003 | Carol | Kisumu Orders: Orders ------ OrderID | CustomerID | OrderDate | Amount O100 | C001 | 2025-01-01 | 500 O101 | C002 | 2025-01-02 | 300 O102 | C004 | 2025-01-03 | 700 Note: C003 has no orders; C004 has orders but is not in Customers. 5.1 Left Outer Join Definition: Returns all rows from the left table (first table) and matching rows from the right table. Unmatched rows from the right table appear as nulls. Retained records: All customers; orders only where CustomerID matches. Example (Customers LEFT OUTER JOIN Orders on CustomerID): CustomerID | CustomerName | Region | OrderID | OrderDate | Amount -----------|--------------|---------|---------|------------|------- C001 | Alice | Nairobi | O100 | 2025-01-01 | 500 C002 | Bob | Mombasa | O101 | 2025-01-02 | 300 C003 | Carol | Kisumu | null | null | null Use case: List all customers and their orders, including customers with no orders. 5.2 Right Outer Join Definition: Returns all rows from the right table and matching rows from the left table. Unmatched rows from the left table appear as nulls. Retained records: All orders; customers only where CustomerID matches. Example (Customers RIGHT OUTER JOIN Orders on CustomerID): CustomerID | CustomerName | Region | OrderID | OrderDate | Amount -----------|--------------|---------|---------|------------|------- C001 | Alice | Nairobi | O100 | 2025-01-01 | 500 C002 | Bob | Mombasa | O101 | 2025-01-02 | 300 C004 | null | null | O102 | 2025-01-03 | 700 Use case: List all orders and their customers, including orders with missing customer records. 5.3 Full Outer Join Definition: Returns all rows from both tables, matching where possible. Unmatched rows from either side appear with nulls for the other table's columns. Retained records: All customers and all orders. Example (Customers FULL OUTER JOIN Orders on CustomerID): CustomerID | CustomerName | Region | OrderID | OrderDate | Amount -----------|--------------|---------|---------|------------|------- C001 | Alice | Nairobi | O100 | 2025-01-01 | 500 C002 | Bob | Mombasa | O101 | 2025-01-02 | 300 C003 | Carol | Kisumu | null | null | null C004 | null | null | O102 | 2025-01-03 | 700 Use case: Audit data to find customers without orders and orders without customers. 5.4 Inner Join Definition: Returns only rows where there is a match in both tables. Retained records: Only customers with orders and only orders with customers. Example (Customers INNER JOIN Orders on CustomerID): CustomerID | CustomerName | Region | OrderID | OrderDate | Amount -----------|--------------|---------|---------|------------|------- C001 | Alice | Nairobi | O100 | 2025-01-01 | 500 C002 | Bob | Mombasa | O101 | 2025-01-02 | 300 Use case: Analyze only completed customer-order pairs. 5.5 Left Anti Join Definition: Returns rows from the left table that have no match in the right table. Retained records: Customers with no orders. Example (Customers LEFT ANTI JOIN Orders on CustomerID): CustomerID | CustomerName | Region -----------|--------------|-------- C003 | Carol | Kisumu Use case: Identify customers who have never placed an order. 5.6 Right Anti Join Definition: Returns rows from the right table that have no match in the left table. Retained records: Orders with no matching customer. Example (Customers RIGHT ANTI JOIN Orders on CustomerID): CustomerID | CustomerName | Region | OrderID | OrderDate | Amount -----------|--------------|---------|---------|------------|------- C004 | null | null | O102 | 2025-01-03 | 700 Use case: Find orphaned orders (e.g., data quality issues). 6. Power Query Joins vs Power BI Relationships It's important to distinguish between merging tables in Power Query and creating relationships in the data model. 6.1 What Each Operation Does Power Query Merge (Join): Physically combines columns from two queries into one query. Happens during the data transformation stage, before loading. Results in a single table in the model (if loaded). Power BI Relationship: Does not merge or combine data physically. Defines a logical link between two separate tables in the data model. Enables filter propagation and coordinated aggregations across tables. 6.2 When to Use Each Use a Power Query merge when: You need a single denormalized table (e.g., for a flat export or specific calculation). You are consolidating 1:1 related tables to simplify the model. You are preparing a curated dataset for a specific report where relationships are not needed. Use relationships when: You are building a BI model with facts and dimensions. You want to keep tables normalized and leverage Power BI's engine for aggregations. You need flexible slicing by multiple dimensions without duplicating data. 6.3 Impact on Model Structure Excessive merging creates wide, denormalized tables, increasing model size and reducing flexibility. Keeping fact and dimension tables separate (star schema) improves: Query performance (better compression, fewer columns scanned). DAX simplicity (clear filter paths, standard time intelligence). Maintainability (changes in dimensions don't require rebuilding large fact tables). Example: Merging FactSales with DimCustomer, DimProduct, and DimDate into one huge table might seem convenient, but it: Increases model size due to repeated dimension values. Makes it harder to reuse dimensions across other facts (e.g., FactTargets). Complicates DAX for time intelligence and advanced calculations. Using relationships preserves a clean star schema and lets Power BI handle the joins efficiently at query time. 7. Recommended Power BI Model for a Typical BI Project For most business intelligence projects, the recommended approach is a star schema with: One or more fact tables (e.g., FactSales, FactOrders). Multiple dimension tables (e.g., DimCustomer, DimProduct, DimDate, DimLocation). One-to-many relationships from dimensions to facts, with single-direction filtering by default. Justification Query and report performance: Star schemas are optimized for Power BI's VertiPaq engine, yielding faster aggregations and lower memory usage compared to flat or heavily snowflaked models. DAX simplicity: Clear 1:* relationships and single-direction filters make DAX measures more straightforward, especially for time intelligence and context manipulation. Model readability: Separating facts and dimensions makes the model intuitive: facts represent "what happened," dimensions represent "how to slice it." Scalability: Adding new facts or dimensions is easier without restructuring a massive flat table. Dimensions can be reused across multiple fact tables (e.g., DimDate for sales and targets). Data redundancy: Star schemas reduce redundancy compared to flat tables by storing dimension attributes once. Maintainability: Changes to dimension attributes (e.g., new region, corrected product category) are made in one place. Easier to document and onboard new developers. Ease of creating reports: Report builders can drag dimensions onto visuals and automatically filter facts, without complex DAX or query logic. Filter propagation: Single-direction 1:* relationships provide predictable filter flow from dimensions to facts, reducing ambiguity. Model complexity: Star schemas strike a balance: more structured than flat tables, but simpler than deeply snowflaked models. Recommended Relationship Design Schema: Star schema as the default. Use snowflaking only when there is a strong, justified need (e.g., very large, shared, normalized dimensions). Relationships: Predominantly one-to-many from dimensions to facts. Avoid many-to-many between facts; use bridge tables if truly needed. Consolidate 1:1 related tables via Power Query merges where appropriate. Filter direction: Single-direction by default. Enable bidirectional filtering sparingly and only after testing for ambiguity and performance. This design provides a robust foundation for scalable, high-performance Power BI solutions that are easy to understand, extend, and maintain.