Introduction When I first started working with Power BI, I thought that importing data and creating charts was the main part of building a report. However, I quickly realized that before creating visuals, I needed to understand how my data was structured and how different tables were connected. A Power BI report can contain beautiful dashboards, but if the underlying data model is poorly designed, the report can become difficult to maintain, DAX calculations can become unnecessarily complicated, and filters may not behave as expected. This article explores three important areas of Power BI: Data modelling and schema design Relationships between tables Joins in Power Query Data Modelling in Power BI. Data modelling is the process of organizing tables and defining how they relate to each other so that Power BI can correctly analyze the data. Instead of putting every piece of information into one large table, we can separate information into logical tables and connect them using relationships. For example, a sales business might have: Customers Products Dates Locations FactSales The FactSales table contains the actual sales transactions, while the other tables provide descriptive information about customers, products, dates, and locations. A good data model is important because it affects: - Reporting & Analytics: Keeps your data organized so finding and dragging fields into charts feels natural and quick. - DAX Calculations: Allows you to write short, simple formulas instead of long, confusing code to connect messy tables. - Performance: Helps Power BI run much faster, so charts load instantly instead of freezing or lagging. - Scalability: Keeps your reports fast and stable, even as your data grows from thousands of rows to millions. - Maintainability: Makes it easy to fix mistakes, update numbers, or share your work with teammates without breaking anything. The Dataset Used in This Project For this project, I used a small sales dataset. The workbook contains the following tables: Table Purpose Customers Customer information CustomerProfiles Customer contact information Segments Customer segment information Products Product information Locations Location information Dates Date attributes Orders Order information OrderDetails Products contained in each order FactSales Central sales fact table Data Modelling Approaches There are several ways to organize data in a Power BI model. The three approaches discussed in this article are: Flat Table Star Schema Snowflake Schema Flat Table A flat table contains most or all of the information required for analysis in a single table.Instead of having separate Customer, Product, Location and Sales tables, the information is stored together. Advantages of a Flat Table A flat table is relatively easy to understand, especially for beginners. Advantages include: Simple structure No relationships required Easy to create simple visuals Easy to export Useful for small datasets Suitable for straightforward analysis Disadvantages The biggest problem is data redundancy. For example, if a customer makes 100 purchases, their name, city, county and other descriptive information may be repeated 100 times. This can result in: Larger models Repeated data More difficult maintenance More opportunities for inconsistent information Less scalable models A flat table is therefore appropriate when the dataset is small, the analysis is simple, the data already comes in a clean denormalized format, there are no complex relationships, or the report is temporary or exploratory. Star Schema A star schema separates the business event from the descriptive information. At the center is a fact table, surrounded by dimension tables. For this project, the model can be structured as: Advantages of a Star Schema A star schema has several advantages: Easy to understand Clear separation between facts and dimensions Simple relationships DAX is generally easier to write Good report performance Easy filter propagation Scales well Easier to maintain Reduces unnecessary duplication Disadvantages There are some disadvantages: More tables need to be managed Relationships must be correctly configured Beginners may initially find the model more complicated than a flat table Poor relationship design can produce incorrect results When Should a Star Schema Be Used? A star schema is particularly useful for: Business intelligence Sales reporting Financial reporting Inventory analysis Customer analytics Operational dashboards Large analytical models Snowflake Schema A snowflake schema is similar to a star schema, but dimension tables are further divided into additional related tables. Advantages A snowflake schema can: Reduce repeated descriptive data Normalize dimensions Improve consistency in some situations Be useful when dimensions have complex hierarchies Disadvantages The main disadvantage is increased complexity. This includes: More tables More relationships More joins/relationship paths Potentially more complicated DAX More complicated filter propagation It can be therefore be approriate when: Dimensions are very large Hierarchies are complex Data is already highly normalized Reducing redundancy is important There are reusable sub-dimensions Understanding Fact Tables and Dimension Tables One of the most important concepts in data modelling is understanding the difference between fact tables and dimension tables. A fact table stores information about business events or transactions.It contains numerical values that can be aggregated. In my data set the Fact Sales is the fact table containing OrderID,OrderDate,CustomerID,ProductID,LocationID,Quantity,Price, Revenue,Cost,Profit and Status. Dimension tables contain descriptive attributes used to describe and filter the business events in the fact table. Examples include:CustomerID,CustomerName,City,County and SegmentID. In simple terms: Dimensions answer questions such as: Who? What? Where? When? Facts answer questions such as: How many? How much? What revenue? What cost? What profit? Understanding Granularity (Grain) in Data Modeling When designing a data model, one of the first questions you should ask is: What does one row in this table represent? The answer to this question is called the grain of the table. Grain defines the exact level of detail stored in each row of a fact table. In other words, it tells us what a single record represents. Practical Example: Building a Star Schema for a Retail Business Imagine a retail company that sells clothing through several branches. The business wants to use Power BI to analyze its sales performance.Instead of putting all the information into one large table, we can organize the data using a star schema. At the center of the model is the Fact Sales table. This table contains the sales transactions and the numerical information we want to analyze, such as quantity sold, sales revenue, and discounts. Around the fact table are several dimension tables that provide additional information about the sales, such as Dim Customer, Dim Product, Dim Store, and Dim Date. Connecting the Tables Using Keys We can use the Fact Sales and Dim Product tables to see how the connection works. The Product_Key in Dim Product is the primary key. It uniquely identifies each product in the table. The Product_Key in Fact Sales is the foreign key. It is used to link each sale to the correct product in the Dim Product table. For example, we could have: Dim Product Product_Key Product_Name Category P001 Classic Shirt Shirts P002 Wide Leg Pants Trousers Fact Sales Sale_ID Product_Key Quantity Sales 1001 P001 2 8,000 1002 P002 1 5,000 1003 P001 3 12,000 These two tables are connected through Product_Key. If Power BI sees P001 in the Fact Sales table, it can use that key to find P001 in the Dim Product table and identify the product as Classic Shirt. The relationship is: Dim Product (1) → Fact Sales (*) This means that one product can appear in many sales transactions. Understanding the Keys Primary Key: A column that uniquely identifies each record in a table. In our example, Product_Key uniquely identifies each product in Dim Product. Foreign Key: A column that refers to the primary key of another table. In our example, Product_Key in Fact Sales links each sale to a product in Dim Product. The same approach can be used to connect other dimension tables, such as Dim Customer, Dim Store, and Dim Date, to the Fact Sales table. Together, these tables form a star schema. Relationships in Power BI A relationship tells Power BI how two tables are connected.Relationships are necessary because analytical information is often distributed across multiple tables. One-to-Many Relationship (1:*) The most common relationship in a Power BI star schema is one-to-many.A single unique key in one table (the "One" side) matches multiple instances of that same key in another table (the "Many" side). Practical Example: Connecting DimCustomer[CustomerID] to FactSales[CustomerID]. A customer registers once (unique on the "One" side), but can make multiple purchases over time (appears multiple times on the "Many" side). When to use: This is the gold standard for Power BI data modeling. Use it to connect all dimension tables to your central fact table. When NOT to use: Never force a $1:* relationship if the key on the "One" side contains duplicates; Power BI will throw an error or default to a Many-to-Many relationship. One-to-One Relationship(1:1) Each unique value in the primary key column of Table A corresponds to exactly one unique value in Table B, and vice versa. Practical Example: Connecting DimCustomer to CustomerContactInfo, where both tables use CustomerID as a unique primary key. When to use: **Use sparingly when splitting a very wide table into two for security reasons (e.g., separating sensitive HR data) or organizational performance. When NOT to use: In almost all standard Power BI scenarios, 1:1 relationships add unnecessary model complexity. It is far better to merge the two tables into a single table using Power Query before loading the model. Many-to-Many Relationship(:) Neither column in the relationship contains unique values. Multiple records in Table A match multiple records in Table B. Practical Example: Connecting FactSales directly to a Promotions table where a single sale can qualify for multiple promotions, and a single promotion applies to multiple sales transactions. When to use: Use only when modeling complex, non-additive business scenarios that cannot be restructured in Power Query (such as target vs. actuals at higher aggregation levels). When NOT to use: Avoid using direct : relationships between standard facts and dimensions. They introduce ambiguous filter paths, create unpredictable DAX calculation results, and significantly reduce query performance. Instead, resolve them by introducing a Bridge Table in between to create two 1:* relationships. Other Important Relationship Concepts Primary Keys and Foreign Keys A primary key is a column that uniquely identifies each record in a table. In the Customers table, CustomerID acts as the primary key because each customer has a unique ID. A foreign key is a column that references a key in another table. In FactSales, CustomerID acts as a foreign key. Unlike the primary key, the foreign key does not have to be unique. Cardinality Cardinality describes how many records in one table can be associated with records in another table. Power BI supports several types of cardinality, with three being particularly important when designing a model. Unique Values and Referential Integrity For a column to be used on the one side of a 1:* relationship, its values must be unique. For example: Customers[CustomerID] C001 C002 C003 C004 C005 C006 There should not be multiple records with C001 in this table. The corresponding foreign key in FactSales, however, can contain repeated values: FactSales[CustomerID] C001 C002 C003 C001 Referential integrity refers to the consistency between the values in the related tables. Ideally, every foreign-key value in the fact table should have a corresponding key in the dimension table.For example: FactSales[CustomerID] → Customers[CustomerID] Active and Inactive Relationships Power BI relationships can be active or inactive. An active relationship is used automatically when filters and calculations are evaluated. For example:DimDate[Date] ─────── FactSales[OrderDate] Active If a report user selects January 2026 from DimDate, the active relationship allows Power BI to filter the relevant sales records. An inactive relationship exists in the model but is not automatically used.This can be useful when a fact table contains multiple date columns, such as: FactSales --------- OrderDate ShipDate DeliveryDate A model could contain relationships from a Date dimension to both OrderDate and ShipDate, while only one is active. The inactive relationship can then be used when a calculation specifically needs the alternative date. Filter Direction in Power BI Relationships determine not only how tables are connected but also how filters move between them. Consider the following relationship: If a user selects Bags from DimProduct, the filter travels to FactSales. Single-Direction Filtering In a typical star schema, filters flow from the dimension tables toward the fact table. For example, selecting: Product Category = Bags, filters the products in DimProduct, which then filters the related records in FactSales. Single-direction filtering is generally preferred because the direction of filter propagation is clear and predictable. Bidirectional Filtering With bidirectional filtering, filters can flow in both directions between related tables. Bidirectional filtering can be useful for specific modelling requirements, but it should not automatically be enabled throughout a model. onsider a model containing several interconnected tables: There may now be multiple paths through which a filter can travel. This can result in: Ambiguous filter paths Unexpected filtering More difficult DAX calculations Harder-to-understand report behaviour Unnecessary model complexity For this reason, my default approach would be single-direction filtering from dimension tables to fact tables, unless there is a clear business requirement for bidirectional filtering. Joins in Power Query A join combines information from two tables based on one or more matching columns. In Power Query, joins are performed using Merge Queries. For example, consider two tables from the practice dataset. The common column is CustomerID. Different join types produce different results depending on which records we want to retain. Left Outer Join A Left Outer Join keeps all records from the left (First) table and any matching records from the right (Second) table. I used Customers as the left table and Orders as the right table. The Left Outer Join retained every customer, including C006, even though this customer had no corresponding order. The order information was added where a matching CustomerID existed. Right Outer Join Keeps every row from the right (second) table, plus matches from the left( First) table where they exist. The Right Outer Join retained every record from the Orders table. As a result, O1008 remained in the output even though its CustomerID, C999, did not exist in the Customers table. Inner Join Combines two tables by keeping only the rows that have matching values in a common column. The Inner Join returned only records where CustomerID existed in both tables. Therefore, customers without orders and orders with invalid customer IDs were excluded. Full Outer Join Combines two tables by keeping all rows from both tables, whether they match or not. The Full Outer Join retained all records from both tables. This made it possible to see both types of mismatch: a customer with no order and an order whose customer does not exist in the Customers table. Left Anti Join Returns only the rows from the first (left) table that do not have a match in the second (right) table. The Left Anti Join returned records that existed in Customers but had no matching CustomerID in Orders. In this dataset, the result was C006, Faith Chebet. This type of join can be used to identify customers who have not placed an order. Right Anti Join Returns only the rows from the right-hand (secondary) table that have no matching rows in the left-hand (primary) table. The Right Anti Join returned records from Orders that had no corresponding CustomerID in Customers. The result identified O1008 with CustomerID C999, revealing a referential-integrity issue in the dataset. Power Query Joins vs. Power BI Relationships A Power Query merge and a Power BI relationship both connect tables, but they work differently and are used at different stages of the Power BI workflow. Does a Power Query Merge Physically Combine Data? Yes. A merge in Power Query combines columns from two tables into one table based on a matching key. For example, if we merge Customers with Orders using CustomerID, customer information such as name and location can be added directly to the Orders table. This happens during the data preparation stage, before the data is loaded into the Power BI data model. Does Creating a Relationship Combine the Tables? No. A relationship keeps the tables separate. For example, DimCustomer and FactSales remain as two separate tables in the Power BI model. The relationship simply tells Power BI that the tables are connected through a common key. For example: DimCustomer[CustomerID] → FactSales[CustomerID] Power BI can then use this relationship to connect customer information with sales when creating reports and calculations. When Does Each Operation Happen? The two operations happen at different stages: Merge: Done in Power Query during data preparation and transformation. Relationship: Created in the Power BI Data Model after the tables have been loaded. A simple way to remember this is: Power Query prepares and transforms the data, while the Data Model connects the tables for analysis. When Would You Choose a Merge Instead of a Relationship? A merge can be useful when you genuinely need to create one combined table. For example, you might need a single flat table for a specific export or for a system that does not support relationships. However, for most business intelligence reporting, especially when building a star schema, relationships are generally preferred. How Can Excessive Merging Affect a Data Model? Merging everything into large tables can reintroduce the redundancy that a star schema is designed to avoid. For example, if customer information is merged into a sales table, the same customer name, location, and other details may be repeated across every transaction made by that customer. As the dataset grows, this can create wider and more difficult-to-maintain tables. It can also make the overall data model harder to understand because the clear separation between facts and dimensions is lost. Why Keep Fact and Dimension Tables Separate? Keeping the tables separate has several advantages: Less redundancy: Customer information is stored once in DimCustomer rather than repeated for every sale. Cleaner data model: Fact and dimension tables have clearly defined purposes. Simpler analysis: Measures can be created from the fact table while dimensions provide the context for analysis. Easier maintenance: If a customer's location changes, you update it in one place instead of updating many transaction rows. Better scalability: A well-designed star schema is easier to work with as the amount of data increases. A Practical Example Suppose Alice Wambui has made two orders: Order_ID CustomerID Sales 101 C001 5,000 104 C001 8,000 In the DimCustomer table, Alice's information is stored once: CustomerID Customer_Name Location C001 Alice Wambui Nairobi If we merge the customer table into the sales table, Alice's name and location would be repeated on both orders. If we instead create a relationship, the customer information stays in DimCustomer, while FactSales only needs the CustomerID. DimCustomer FactSales CustomerID CustomerID Alice Wambui C001 Nairobi C001 | | └──────── 1 : * ──────────┘ Power BI uses the relationship between CustomerID and CustomerID to connect the two tables when analyzing the data. Key Takeaway The main difference is simple: A Power Query merge combines data into a table, while a Power BI relationship connects separate tables within the data model. For a well-structured star schema, keeping fact and dimension tables separate and connecting them with relationships is usually the better approach. Recommended Power BI Model For a typical business intelligence project, I would recommend using a star schema as the preferred data model. A star schema provides a good balance between performance, simplicity, scalability, and maintainability. It separates business transactions from descriptive information, making the model easier to understand and use when building Power BI reports. A typical model could look like this: DimProduct | | DimCustomer ─────── FactSales ─────── DimStore | | DimDate The FactSales table would contain the business events and numerical values we want to analyze, such as sales amount, quantity, cost, and profit. The dimension tables would contain descriptive information such as product details, customer information, store details, and dates. Why Choose a Star Schema? 1. Better Query and Report Performance A star schema keeps dimension tables relatively small and the fact table focused on transactions and measures. This structure allows Power BI to efficiently filter and aggregate data when users interact with reports. Instead of storing customer or product information repeatedly in every sales transaction, the information is stored in the relevant dimension table and connected through relationships. 2. Simpler DAX Star schemas also make DAX easier to understand. For example, a measure can calculate total sales from the FactSales table: Total Sales = SUM(FactSales[Sales_Amount]) Users can then analyze this measure by product category, store, customer type, or month using fields from the dimension tables. The relationships allow the filters from those dimensions to affect the fact table automatically. 3. Better Model Readability A star schema has a clear structure. The fact table sits at the center, while the dimension tables surround it. This makes it easier for another analyst to understand the model and identify where different types of information are stored. For example: FactSales → What happened? DimProduct → What was sold? DimCustomer → Who bought it? DimStore → Where was it sold? DimDate → When did it happen? 4. Scalability A business may start with thousands of transactions and eventually grow to millions. Keeping fact and dimension tables separate provides a structure that can scale more easily as the amount of data increases. It also makes it easier to add new dimensions or attributes without completely restructuring the fact table. 5. Less Data Redundancy A star schema avoids unnecessarily repeating descriptive information. For example, instead of storing "Alice Wambui, Nairobi" on every transaction made by Alice, that information can be stored once in DimCustomer. The fact table only needs the CustomerID to identify the customer.This reduces redundancy and keeps the model organized. 6. Easier Maintenance If a customer's location changes, we can update the information in DimCustomer rather than changing it across thousands of sales records. Similarly, if a product is moved to a different category, the relevant information can be maintained in DimProduct. This makes the model easier to manage over time. Recommended Relationships For a typical star schema, I would normally use one-to-many (1:*) relationships between dimension tables and the fact table. For example: DimProduct[Product_Key] 1 ───── * FactSales[Product_Key] DimCustomer[Customer_Key] 1 ───── * FactSales[Customer_Key] DimStore[Store_Key] 1 ───── * FactSales[Store_Key] DimDate[Date_Key] 1 ───── * FactSales[Date_Key] The dimension table is on the "one" side, because each key should uniquely identify a record in that dimension. The fact table is on the "many" side, because the same product, customer, store, or date can appear in many transactions. Filter Direction I would normally use single-direction filtering, with the filter flowing from the dimension table to the fact table: DimProduct ↓ FactSales For example, if a user selects Shirts from DimProduct, the filter flows to FactSales and Power BI calculates the sales for shirts. Single-direction filtering is generally preferable because it keeps the model's behavior predictable and reduces unnecessary complexity. Bidirectional filtering can be useful in specific scenarios, but I would not use it by default. Using it unnecessarily can create ambiguous filter paths and make a model more difficult to understand and troubleshoot. What About a Flat Table or Snowflake Schema? A flat table can be useful for simple datasets or small reporting tasks because everything is contained in one table. However, as the dataset grows, it can create a lot of repeated information and become difficult to maintain. A snowflake schema normalizes dimensions into additional related tables. This can reduce redundancy, but it also introduces more relationships and complexity into the model. For most Power BI business intelligence projects, I would therefore choose a star schema because it provides a practical middle ground: Simple enough to understand, efficient enough to perform well, and flexible enough to scale. Final Recommendation For a typical Power BI project, my preferred design would be: Star schema One central fact table Multiple dimension tables One-to-many relationships from dimensions to the fact table Single-direction filter propagation from dimensions to facts Clearly defined grain for the fact table Measures stored and calculated from the fact table Descriptive attributes stored in dimension tables Avoid unnecessary merging of fact and dimension tables Conclusion The goal is not simply to create relationships between tables, but to create a model that is easy to understand, efficient to query, simple to maintain, and flexible enough to support future reporting needs. Ultimately, a good Power BI model should make it easier for both the developer and the end user to answer business questions without introducing unnecessary complexity.