{"id":5195,"date":"2026-09-14T01:38:57","date_gmt":"2026-09-14T04:38:57","guid":{"rendered":"https:\/\/tucumandevelopers.com\/index.php\/2026\/09\/14\/building-a-power-bi-data-model-for-shopke-understanding-relationships-schemas-and-joins\/"},"modified":"2026-09-14T01:38:57","modified_gmt":"2026-09-14T04:38:57","slug":"building-a-power-bi-data-model-for-shopke-understanding-relationships-schemas-and-joins","status":"publish","type":"post","link":"https:\/\/tucumandevelopers.com\/index.php\/2026\/09\/14\/building-a-power-bi-data-model-for-shopke-understanding-relationships-schemas-and-joins\/","title":{"rendered":"Building a Power BI Data Model for ShopKe: Understanding Relationships, Schemas and Joins"},"content":{"rendered":"<div>\n<div>\n<p><strong>Structure<\/strong> <\/p>\n<div>\n<pre><code>\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 SalesData \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 OrderID \u2502 \u2502 CustomerName \u2502 \u2502 ProductName \u2502 \u2502 Category \u2502 \u2502 Quantity \u2502 \u2502 SalesAmount \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 <\/code><\/pre>\n<div>\n<\/p><\/div>\n<\/p><\/div>\n<p><strong>Advantages<\/strong>: Simple to understand, easy to import and good for small datasets.<\/p>\n<p><strong>Disadvantages<\/strong>: Repeated information increases redundancy and the table may become difficult to maintain as the business grows.<\/p>\n<p><strong>When to use<\/strong>: Small datasets, quick analysis and simple reporting requirements.<\/p>\n<p><strong>Performance and complexity<\/strong>: It has few relationship complications, but unnecessary columns and repeated values can increase the amount of data stored.<\/p>\n<h2> <a name=\"12-star-schema\" href=\"#12-star-schema\"> <\/a> 1.2 Star Schema <\/h2>\n<p>A <strong>star schema<\/strong> contains a central fact table connected directly to multiple dimension tables.<\/p>\n<p>For ShopKe, FactSales stores transactions, while DimCustomer, DimProduct and DimDate provide descriptive information.<\/p>\n<p><strong>Structure<\/strong><\/p>\n<p><a href=\"https:\/\/media2.dev.to\/dynamic\/image\/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto\/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fbp859fbatv3p9ofpzwh1.png\"><\/a><\/p>\n<p><em>ShopKe data model presented as a star schema<\/em><\/p>\n<p><strong>Advantages<\/strong>: Simple to understand, supports readable DAX, reduces unnecessary duplication and well suited for analytical reporting.<\/p>\n<p><strong>Disadvantages<\/strong>: Requires careful relationship design and may involve some repeated descriptive information within dimensions.<\/p>\n<p><strong>When to use<\/strong>: Sales, finance, inventory, customer and other business intelligence projects.<\/p>\n<p><strong>Performance and complexity<\/strong>: A well-designed star schema can support efficient analytical queries and compression while keeping the model relatively easy to navigate.<\/p>\n<h2> <a name=\"13-snowflake-schema\" href=\"#13-snowflake-schema\"> <\/a> 1.3 Snowflake Schema <\/h2>\n<p>A <strong>snowflake schema<\/strong> is a type of star schema where dimension tables are further divided into related tables.<\/p>\n<p>For example, ShopKe may separate product information into Product, Subcategory and Category tables.<\/p>\n<p><strong>Structure<\/strong> <\/p>\n<div>\n<pre><code> \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 DimCategory \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 \u2502 1 \u2502 * \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u25bc\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502DimSubcategory\u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 \u2502 1 \u2502 * \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u25bc\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 FactSales \u2502 *\u2500\u25001\u2502 DimProduct \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 <\/code><\/pre>\n<div>\n<\/p><\/div>\n<\/p><\/div>\n<p><a href=\"https:\/\/media2.dev.to\/dynamic\/image\/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto\/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fwfx86u5i2qyglzyr20l6.png\"><\/a><\/p>\n<p><em>An example of how a snowflake schema model would look like<\/em><\/p>\n<p><strong>Advantages<\/strong>: Reduces repeated descriptive information and can support difficult hierarchies.<\/p>\n<p><strong>Disadvantages<\/strong>: More tables and relationships increase model complexity and may make report development less intuitive.<\/p>\n<p><strong>When to use<\/strong>: Complex organizational or geographical hierarchies, normalized data sources and situations where shared reference tables are useful.<\/p>\n<p><strong>Performance and complexity<\/strong>: It can reduce redundancy, but more relationships may make the model more complicated. In many cases, a star schema is preferred for simplicity.<\/p>\n<h2> <a name=\"2-fact-tables-and-dimension-tables\" href=\"#2-fact-tables-and-dimension-tables\"> <\/a> 2. Fact Tables and Dimension Tables <\/h2>\n<h3> <a name=\"fact-tables\" href=\"#fact-tables\"> <\/a> Fact tables <\/h3>\n<p>A fact table stores measurable business events. In ShopKe, FactSales contains individual sales transactions.<\/p>\n<p>Example fields include:<\/p>\n<ul>\n<li>SalesID<\/li>\n<li>CustomerID<\/li>\n<li>ProductID<\/li>\n<li>DateKey<\/li>\n<li>Quantity<\/li>\n<li>SalesAmount<\/li>\n<\/ul>\n<p><a href=\"https:\/\/media2.dev.to\/dynamic\/image\/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto\/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fc0kh5fzjgyrtgavteivs.png\"><\/a><br \/> <em>The FactSales table<\/em><\/p>\n<h2> <a name=\"dimension-tables\" href=\"#dimension-tables\"> <\/a> Dimension tables <\/h2>\n<p>Dimension tables store descriptive attributes used to analyze facts.<\/p>\n<p>Examples include:<\/p>\n<ul>\n<li> <strong>DimCustomer<\/strong> containing customer names and regions.<\/li>\n<li> <strong>DimProduct<\/strong> containing product names and categories.<\/li>\n<li> <strong>DimDate<\/strong> containing dates, months and years.<\/li>\n<\/ul>\n<h2> <a name=\"grain-of-a-fact-table\" href=\"#grain-of-a-fact-table\"> <\/a> Grain of a fact table <\/h2>\n<p>The <strong>grain<\/strong> describes what one row represents.<\/p>\n<p>For example:<\/p>\n<blockquote>\n<p>One row in <strong>FactSales<\/strong> represents one product line in one customer order.<\/p>\n<\/blockquote>\n<p>This is important because an order may contain several product lines. Therefore, counting rows may not equal counting unique orders.<\/p>\n<h2> <a name=\"shopkes-star-schema\" href=\"#shopkes-star-schema\"> <\/a> ShopKe&#8217;s star schema <\/h2>\n<p>FactSales connects to the dimensions through keys:<\/p>\n<p>DimCustomer<code>[CustomerID]<\/code> 1 \u2500\u2500\u2500 * FactSales<code>[CustomerID]<\/code><\/p>\n<p>DimProduct<code>[ProductID]<\/code> 1 \u2500\u2500\u2500 * FactSales<code>[ProductID]<\/code><\/p>\n<p>DimDate<code>[DateKey]<\/code> 1 \u2500\u2500\u2500 * FactSales<code>[DateKey]<\/code><\/p>\n<p>DimLocation<code>[LocationID]<\/code> 1 \u2500\u2500\u2500 * FactSales<code>[LocationID]<\/code><\/p>\n<p>This allows ShopKe to analyze total sales by product, customer or date.<\/p>\n<h2> <a name=\"3-relationships-in-power-bi\" href=\"#3-relationships-in-power-bi\"> <\/a> 3. Relationships in Power BI <\/h2>\n<p>A <em>relationship<\/em> connects tables by use of matching columns. Relationships are necessary because business data is often distributed across several tables.<\/p>\n<h3> <a name=\"primary-keys-and-foreign-keys\" href=\"#primary-keys-and-foreign-keys\"> <\/a> Primary keys and foreign keys <\/h3>\n<p>A <strong>primary<\/strong> key uniquely identifies a row in a table. A <strong>foreign key<\/strong> references a key in another table.<\/p>\n<p>For example, <strong>CustomerID<\/strong> is unique in DimCustomer but may repeat in FactSales because one customer can make multiple purchases.<\/p>\n<p>This creates a <strong>one to many relationship<\/strong>.<\/p>\n<h2> <a name=\"relationship-cardinalities\" href=\"#relationship-cardinalities\"> <\/a> Relationship cardinalities <\/h2>\n<h3> <a name=\"one-to-many-1\" href=\"#one-to-many-1\"> <\/a> One to Many (1:*) <\/h3>\n<p>One row in a dimension relates to many rows in a fact table.<\/p>\n<p>Example:<\/p>\n<p>DimCustomer<code>[CustomerID]<\/code> 1 \u2500\u2500\u2500 * FactSales<code>[CustomerID]<\/code><\/p>\n<p>This is the most common relationship in a star schema.<\/p>\n<h3> <a name=\"one-to-one-11\" href=\"#one-to-one-11\"> <\/a> One to One (1:1) <\/h3>\n<p>Each row in one table matches at most one row in another table.<\/p>\n<p>Example:<\/p>\n<p>Employee<code>[EmployeeID]<\/code> 1 \u2500\u2500\u2500 1 EmployeeDetails<code>[EmployeeID]<\/code><\/p>\n<p>It is appropriate when two tables contain separate information about the same entity and both sides have unique keys.<\/p>\n<h3> <a name=\"many-to-many-\" href=\"#many-to-many-\"> <\/a> Many to Many (:) <\/h3>\n<p>Multiple rows in one table can relate to multiple rows in another table.<\/p>\n<p>Example:<\/p>\n<p>Students * \u2500\u2500\u2500 * Courses<\/p>\n<p>One student can take many courses and one course can have many students.<\/p>\n<p>A bridge table can be used for a clearer design as shown:<\/p>\n<p>DimCustomer 1 \u2500\u2500\u2500 * BridgeCustomerSegment * \u2500\u2500\u2500 1 DimSegment<br \/> Referential integrity and unique values<\/p>\n<p><strong>Referential integrity<\/strong> means foreign keys should correspond to valid keys in the related dimension, where applicable.<\/p>\n<p>For example, every CustomerID in FactSales should also exist in DimCustomer. Missing or unmatched keys may indicate data quality problems.<\/p>\n<h2> <a name=\"active-and-inactive-relationships\" href=\"#active-and-inactive-relationships\"> <\/a> Active and inactive relationships <\/h2>\n<p>An <strong>active relationship<\/strong> is used automatically for filter propagation.<\/p>\n<p>An <strong>inactive relationship<\/strong> is not used automatically but can be activated in a DAX measure.<\/p>\n<p>For example, FactSales may have <code>OrderDate<\/code> and <code>ShipDate<\/code>. DimDate can have an <strong>active relationship<\/strong> with <code>OrderDate<\/code> and an <strong>inactive relationship<\/strong> with <code>ShipDate<\/code>.<\/p>\n<h2> <a name=\"4-filter-direction\" href=\"#4-filter-direction\"> <\/a> 4. Filter Direction <\/h2>\n<p>Filter direction determines how <strong>filters move between related tables<\/strong>.<\/p>\n<h3> <a name=\"singledirection-filtering\" href=\"#singledirection-filtering\"> <\/a> Single-direction filtering <\/h3>\n<p>Filters flow in one direction, mostly from dimensions to facts.<\/p>\n<p>DimProduct \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500&gt; FactSales<\/p>\n<p>If a user selects Electronics in a product slicer, Power BI filters FactSales to show sales for electronics products.<\/p>\n<p>Single-direction filtering is recommended for star schemas because it provides predictable behavior and reduces ambiguity.<\/p>\n<h3> <a name=\"bidirectional-filtering\" href=\"#bidirectional-filtering\"> <\/a> Bidirectional filtering <\/h3>\n<p>Filters flow in both directions.<\/p>\n<p>DimProduct &lt;\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500&gt; FactSales<\/p>\n<p>This may be useful in specific scenarios, such as certain bridge-table models.<\/p>\n<p>However, bidirectional filtering should be used carefully because it can create ambiguous filter paths, unexpected results and unnecessary model complexity.<\/p>\n<p>For ShopKe, I would use single-direction filtering by default and only introduce bidirectional filtering when there is a clear business requirement.<\/p>\n<h2> <a name=\"5-joins-in-power-query\" href=\"#5-joins-in-power-query\"> <\/a> 5. Joins in Power Query <\/h2>\n<p>A join combines rows from two tables using matching columns. In Power Query, this is performed using <strong>Merge Queries<\/strong>.<\/p>\n<p>If ShopKe had the following tables:<\/p>\n<p><strong>DimCustomers<\/strong><\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>CustomerID<\/th>\n<th>CustomerName<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<\/tr>\n<tr>\n<td>C002<\/td>\n<td>Brian<\/td>\n<\/tr>\n<tr>\n<td>C003<\/td>\n<td>Aisha<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><strong>DimOrders<\/strong><\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>OrderID<\/th>\n<th>CustomerID<\/th>\n<th>Amount<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>O001<\/td>\n<td>C001<\/td>\n<td>75,000<\/td>\n<\/tr>\n<tr>\n<td>O002<\/td>\n<td>C001<\/td>\n<td>4,500<\/td>\n<\/tr>\n<tr>\n<td>O003<\/td>\n<td>C002<\/td>\n<td>3,000<\/td>\n<\/tr>\n<tr>\n<td>O004<\/td>\n<td>C004<\/td>\n<td>2,000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>C003 has no order, while C004 appears in Orders but not Customers.<\/p>\n<h3> <a name=\"51-left-outer-join\" href=\"#51-left-outer-join\"> <\/a> 5.1 Left Outer Join <\/h3>\n<p>Returns all rows from the left table and matching rows from the right table.<\/p>\n<p>Example: Customers LEFT JOIN Orders.<\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>CustomerID<\/th>\n<th>CustomerName<\/th>\n<th>OrderID<\/th>\n<th>Amount<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O001<\/td>\n<td>75,000<\/td>\n<\/tr>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O002<\/td>\n<td>4,500<\/td>\n<\/tr>\n<tr>\n<td>C002<\/td>\n<td>Brian<\/td>\n<td>O003<\/td>\n<td>3,000<\/td>\n<\/tr>\n<tr>\n<td>C003<\/td>\n<td>Aisha<\/td>\n<td>NULL<\/td>\n<td>NULL<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><strong>Use<\/strong>: Finding customers who have not placed orders while retaining all customers.<\/p>\n<h3> <a name=\"52-right-outer-join\" href=\"#52-right-outer-join\"> <\/a> 5.2 Right Outer Join <\/h3>\n<p>Returns all rows from the right table and matching rows from the left table.<\/p>\n<p>Example: Customers RIGHT JOIN Orders.<\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>CustomerID<\/th>\n<th>CustomerName<\/th>\n<th>OrderID<\/th>\n<th>Amount<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O001<\/td>\n<td>75,000<\/td>\n<\/tr>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O002<\/td>\n<td>4,500<\/td>\n<\/tr>\n<tr>\n<td>C002<\/td>\n<td>Brian<\/td>\n<td>O003<\/td>\n<td>3,000<\/td>\n<\/tr>\n<tr>\n<td>C004<\/td>\n<td>NULL<\/td>\n<td>O004<\/td>\n<td>2,000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><strong>Use<\/strong>: Identifying orders without matching customer records.<\/p>\n<h3> <a name=\"53-full-outer-join\" href=\"#53-full-outer-join\"> <\/a> 5.3 Full Outer Join <\/h3>\n<p>Returns all rows from both tables, including unmatched records.<\/p>\n<p>Expected unmatched records:<\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>CustomerID<\/th>\n<th>CustomerName<\/th>\n<th>OrderID<\/th>\n<th>Amount<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O001<\/td>\n<td>75,000<\/td>\n<\/tr>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O002<\/td>\n<td>4,500<\/td>\n<\/tr>\n<tr>\n<td>C002<\/td>\n<td>Brian<\/td>\n<td>O003<\/td>\n<td>3,000<\/td>\n<\/tr>\n<tr>\n<td>C003<\/td>\n<td>Aisha<\/td>\n<td>NULL<\/td>\n<td>NULL<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Matching records are also retained.<\/p>\n<p><strong>Use<\/strong>: Data reconciliation and identifying missing records in either table.<\/p>\n<h3> <a name=\"54-inner-join\" href=\"#54-inner-join\"> <\/a> 5.4 Inner Join <\/h3>\n<p>Returns only rows with matching values in both tables.<\/p>\n<p>Expected output:<\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>CustomerID<\/th>\n<th>CustomerName<\/th>\n<th>OrderID<\/th>\n<th>Amount<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O001<\/td>\n<td>75,000<\/td>\n<\/tr>\n<tr>\n<td>C001<\/td>\n<td>Stacy<\/td>\n<td>O002<\/td>\n<td>4,500<\/td>\n<\/tr>\n<tr>\n<td>C002<\/td>\n<td>Brian<\/td>\n<td>O003<\/td>\n<td>3,000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><strong>Use<\/strong>: Keeping only customers who have matching orders.<\/p>\n<h3> <a name=\"55-left-anti-join\" href=\"#55-left-anti-join\"> <\/a> 5.5 Left Anti Join <\/h3>\n<p>Returns rows from the left table that have no matching rows in the right table.<\/p>\n<p><strong>Example<\/strong>: Customers LEFT ANTI JOIN Orders.<\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>CustomerID<\/th>\n<th>CustomerName<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>C003<\/td>\n<td>Aisha<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><strong>Use<\/strong>: Finding customers who have never placed an order.<\/p>\n<h3> <a name=\"56-right-anti-join\" href=\"#56-right-anti-join\"> <\/a> 5.6 Right Anti Join <\/h3>\n<p>Returns rows from the right table that have no matching rows in the left table.<\/p>\n<p><strong>Example<\/strong>: Customers RIGHT ANTI JOIN Orders.<\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>OrderID<\/th>\n<th>CustomerID<\/th>\n<th>Amount<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>O004<\/td>\n<td>C004<\/td>\n<td>2,000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><strong>Use<\/strong>: Identifying orders with missing customer records.<\/p>\n<p>In Power Query, a merge may create a nested table column. For joins that return matching records, the column can be expanded to display the required fields.<\/p>\n<h2> <a name=\"6-power-query-joins-vs-power-bi-relationships\" href=\"#6-power-query-joins-vs-power-bi-relationships\"> <\/a> 6. Power Query Joins vs Power BI Relationships <\/h2>\n<p>Even though both connect tables, they serve different purposes.<\/p>\n<div>\n<table>\n<thead>\n<tr>\n<th>Feature<\/th>\n<th>Power Query Merge<\/th>\n<th>Power BI Relationship<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Stage<\/td>\n<td>Data preparation<\/td>\n<td>Data modelling<\/td>\n<\/tr>\n<tr>\n<td>Purpose<\/td>\n<td>Combine data<\/td>\n<td>Connect tables<\/td>\n<\/tr>\n<tr>\n<td>Physically combines columns?<\/td>\n<td>Yes, in the merged query result<\/td>\n<td>No<\/td>\n<\/tr>\n<tr>\n<td>Filter propagation<\/td>\n<td>Not as a model relationship<\/td>\n<td>Yes<\/td>\n<\/tr>\n<tr>\n<td>Common use<\/td>\n<td>Bring ProductCategory into a table<\/td>\n<td>Connect DimProduct to FactSales<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><strong>Example<\/strong><\/p>\n<p>If ShopKe needs to bring <code>ProductCategory<\/code> into FactSales, Power Query Merge can combine the columns.However, if DimProduct already contains <code>ProductCategory<\/code>, keeping it separate and creating a relationship is better.<\/p>\n<p>Too much merging can create a wide table with repeated customer and product information. This may cause increased redundancy and the model being harder to maintain.Keeping fact and dimension tables separate supports a clearer and more scalable star schema.<\/p>\n<h2> <a name=\"7-recommended-power-bi-model-for-shopke\" href=\"#7-recommended-power-bi-model-for-shopke\"> <\/a> 7. Recommended Power BI Model for ShopKe <\/h2>\n<p>For ShopKe, I would recommend a star schema with FactSales connected to DimCustomer, DimProduct and DimDate.<\/p>\n<p>I would use one-to-many relationships from the dimensions to the fact table and single-direction filtering by default.<\/p>\n<p><strong>Why?<\/strong><\/p>\n<ul>\n<li> <strong>Performance<\/strong> : Separating facts and dimensions can support efficient analytical queries.<\/li>\n<li> <strong>DAX simplicity<\/strong> : Measures such as Total Sales can be written directly against FactSales.<\/li>\n<li> <strong>Readability<\/strong> : Each table has a clear purpose.<\/li>\n<li> <strong>Scalability<\/strong> : New transactions, customers and products can be added without redesigning the entire model.<\/li>\n<li> <strong>Maintainability<\/strong> : Descriptive information can be updated in dimensions.<\/li>\n<li> <strong>Report development<\/strong> : Users can easily combine dimension attributes with fact measures.<\/li>\n<\/ul>\n<p>Example measures:<\/p>\n<p>Total Sales =<br \/> SUM(FactSales<code>[SalesAmount]<\/code>)<br \/> Total Orders =<br \/> DISTINCTCOUNT(FactSales<code>[OrderID]<\/code>)<br \/> Average Order Value =<br \/> DIVIDE(<code>[Total Sales]<\/code>, <code>[Total Orders]<\/code>)<\/p>\n<p><a href=\"https:\/\/media2.dev.to\/dynamic\/image\/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto\/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F34orhyqm68et0u4m8xcz.png\"><\/a><\/p>\n<p>I would use bridge tables for genuine many-to-many scenarios and inactive relationships for alternative date analysis when necessary.<\/p>\n<h2> <a name=\"conclusion\" href=\"#conclusion\"> <\/a> Conclusion <\/h2>\n<p>Data modelling is the foundation of a reliable Power BI report. It determines how data is organized, how tables interact and how accurately business questions can be answered.<\/p>\n<p>As much as flat tables are simple and snowflake schemas can support complex structures, a <strong>star schema<\/strong> is most likely the most suitable choice for a business intelligence project.<\/p>\n<p>Through the ShopKe example, we have seen how fact and dimension tables work together, how relationships control filter propagation and how Power Query joins prepare data for analysis.<\/p>\n<p>The most important lesson is that proper Power BI development is not only about creating visuals. It is about designing a model that is accurate, understandable, scalable and useful for decision-making.<\/p>\n<p><strong>A well designed data model turns disconnected data into meaningful business insights<\/strong>.<\/p>\n<\/p><\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>Fuente: <a href=\"https:\/\/dev.to\/smumbi_\/building-a-power-bi-data-model-for-shopke-understanding-relationships-schemas-and-joins-4g3c\">Art\u00edculo original<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Structure \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510 \u2502 SalesData \u2502 \u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524 \u2502 OrderID \u2502 \u2502 CustomerName \u2502 \u2502 ProductName \u2502 \u2502 Category \u2502 \u2502 Quantity \u2502 \u2502 SalesAmount \u2502 \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518 Advantages: Simple to understand, easy to import and good for small datasets. Disadvantages: Repeated information increases redundancy and the table may become difficult to maintain as the business grows. When [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5194,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"webixso_pending_account_ids":""},"categories":[41],"tags":[],"class_list":["post-5195","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-devto"],"jetpack_publicize_connections":[],"_links":{"self":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/posts\/5195","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/comments?post=5195"}],"version-history":[{"count":0,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/posts\/5195\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/media\/5194"}],"wp:attachment":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/media?parent=5195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/categories?post=5195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/tags?post=5195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}