Preventing Duplicate Facts and Modeling the Correct Grain
A sales report shows one million dollars in revenue for the month. You add a product category, and the total remains one million. Then you add a delivery status, and revenue suddenly becomes one point four million. Nothing in the source system changed. The report simply started repeating some sales amounts.
How would you decide whether the problem is a calculation, a visual, or the structure of the data model? A configuration can load successfully and still produce the wrong analytical result. The stronger evidence is whether each table represents the intended business event, whether dimensions filter at the intended level, and whether the totals remain correct when the report is sliced in different ways.
That reasoning belongs within the PL-300 skill of creating fact tables and dimension tables. A fact table records observations or events that can be analyzed. In a sales model, those observations might concern orders, individual order lines, deliveries, or monthly targets.
A dimension provides reusable descriptive context, such as product, customer, date, or location. The labels fact and dimension are useful, but they aren’t enough by themselves. You must also define the grain of every fact table.
Grain means what one row represents. Consider a table containing order number, product, quantity, and sales amount. If one order can contain several products, one row might represent one product line within one order.
That is different from a table where one row represents the entire order. It’s also different from a monthly summary where one row represents a product’s total sales for one month. All three can contain a column named Sales Amount, but those amounts don’t describe events at the same level.
A practical discipline is to write the grain as one sentence. For example, one row in the sales fact table represents one product line on one customer order. That statement forces important questions into the open.
If the table also contains shipment information, can an order line have more than one shipment? If so, shipment isn’t automatically at the same grain. If a monthly target is repeated on every sales line, the target and the sales event aren’t at the same grain either.
The grain statement should be precise enough that you can test it against the rows. One row represents one order isn’t precise when an order may have multiple products. One row represents one product isn’t precise when the same product appears on thousands of orders. One row represents one product line on one order identifies the combined business event much more clearly.
Now connect that fact table to dimensions. A product dimension might provide the product name, category, and brand associated with a product key. A customer dimension might provide descriptive customer attributes associated with a customer key.
Those keys need to represent the level at which filtering is supposed to occur. When a report filters a product category, the intended product keys should identify the corresponding fact rows. The dimension supplies the reusable context, while the fact table supplies the observations being summarized.
This is primarily a semantic-model design decision. Power Query, Power BI’s data acquisition and transformation environment, can shape source data during preparation and refresh. It can remove columns, group rows, or produce a table at a chosen level. Those transformations may be necessary to create the intended fact and dimension tables.
But a report visual can’t repair an undefined grain. Formatting a chart differently won’t change what a fact row represents. A service permission can control who consumes a report, but it doesn’t correct duplicated sales caused by mixing levels of detail.
The requirement therefore comes before the feature choice. If the business wants sales by product, customer, and order date, determine which sales event must be counted and which descriptive dimensions must filter it. Then prepare and model tables that support that level.
Don’t begin by searching for a universal grain setting. There isn’t one choice that fits every analytical requirement. Grain follows the business event being analyzed.
Suppose Northwind Cycles has a Sales table at order-line grain. Each row represents one product line on one order. The business also has a Deliveries table where one row represents one delivery event. An order line can be split between two deliveries. Both tables are legitimate fact tables, but they have different grains.
If someone joins delivery rows directly into the Sales table without accounting for that difference, one sales line may be copied once for each delivery. A two-hundred-dollar line split across two deliveries can appear as two rows carrying the same two-hundred-dollar sales amount. Summing that amount produces four hundred dollars. The source values may all be individually accurate. The misleading result comes from treating delivery events and sales events as though they were one event at one shared grain.
Changing a Data Analysis Expressions calculation, usually called DAX, to hide that duplicated total would address the visible symptom rather than the model’s meaning. A special calculation might appear to work for one visual or one filter. It could fail when the user groups by delivery status, product, or date. Before altering the calculation, restate the grains.
One row in Sales represents one product line on one order. One row in Deliveries represents one delivery event. Those statements reveal why a simple row-for-row combination is unsafe.
Dimensions then provide common descriptive paths for analysis. If both sales and deliveries need to be analyzed by product, the product key used for filtering should identify the intended product level in each fact table. The same principle applies to customer or date context when the business requirements call for those filters. Reusable dimensions help report consumers use consistent descriptive context instead of relying on separate, conflicting labels embedded independently in each fact table.
Conformance here isn’t achieved because two columns happen to share a name. A Product Key column in two fact tables is useful only if the values represent the product level at which filtering is intended. If one table uses individual products while another stores only product categories, naming both columns Product Key doesn’t make their grains equivalent. The dimension keys and the business meanings must align with the required filtering level.
Imagine that an analyst builds a category dimension with one row per category, but the sales requirement calls for analysis by individual product. The category key can filter category-level data, but it can’t distinguish one product from another. The dimension is too coarse for that requirement. The reverse mismatch also matters. If a monthly target exists only by category, presenting it as though it were an independently assigned target for every product can repeat or misstate the target.
This is why grain should be decided before accepting relationships and calculations. Ask what each fact row means. Ask which dimensions must describe that row. Then check whether the keys on the fact table point to the level where those descriptions are valid. A model may allow columns to be connected without proving that their business meanings conform.
Return to the revenue increase from one million to one point four million. The first observable symptom is that adding delivery status changes a sales total that should remain based on sales events. Reproduce the condition deliberately. Start with total revenue under a known month filter.
Add product category and note the total. Then include delivery status and compare the result. The purpose isn’t to trust one attractive number. It’s to identify exactly which filtering condition exposes the inconsistency.
Next, compare expected and actual behavior. If total revenue should count each sales line once, the expected result follows the sales grain. If a line was delivered in two separate events, delivery analysis may legitimately show two delivery rows, but that doesn’t create a second sale. The actual result of one point four million suggests that some sales observations are being repeated or filtered through data at another grain.
Inspect the layer that owns that behavior. First confirm the query output. Did a Power Query merge produce multiple rows from one sales row? Count or profile the resulting business keys at the intended sales grain. If a sales-line identifier that should describe one observation now appears on several rows, the query output itself contains evidence of grain expansion.
If the query output preserves the intended rows, inspect the semantic model. Do the fact tables and dimensions use keys at the filtering level required by the report? Does the path from delivery status to the displayed result match the business question? The useful question isn’t merely whether a relationship exists. It’s whether filtering through that relationship preserves the meaning of the measure being shown.
Then inspect the DAX result in context. A revenue calculation should be tested under the same month, product, and delivery filters that exposed the problem. If the result is right without delivery status but wrong with it, that contrast is evidence. It points toward a difference in filter behavior or grain rather than a random source error. Changing colors, labels, or visual types won’t address that cause.
The correction should preserve both business events instead of forcing them into an artificial common row. Sales can remain at sales-line grain, and deliveries can remain at delivery-event grain. Reusable dimensions can provide descriptive context at levels each fact table can support. The exact model design still depends on the requirement. The governing rule is that every fact has a declared grain and every dimension key represents the level where filtering is intended.
After correcting the cause, repeat both the ordinary and failing cases. Verify the monthly total without delivery status. Verify it by product category.
Then verify the behavior under the delivery condition that previously caused the increase. A fix isn’t proven because a configuration dialog accepted it or because the model refreshed without an error. It’s proven when the observable result matches the defined business meaning across the relevant conditions.
The same reasoning applies when facts are summarized at different time levels. Suppose Sales records individual order lines, while Budget stores one row per category per month. The sales grain and budget grain are both valid, but they’re not interchangeable.
A daily date selection can filter individual sales events. A monthly budget has no independent daily detail unless the business provides a rule that creates it. Repeating the full monthly budget on every day or every sales row would make a detailed report look populated while producing misleading totals.
Write both statements before modeling the comparison. One row in Sales represents one product line on one customer order. One row in Budget represents one product category’s budget for one month.
Now the allowable analysis is clearer. Product-level actual sales can be summarized to category and month for comparison with the budget. Treating a category-month budget as though it originated at product-line grain would claim detail the budget table doesn’t contain.
Dimensions must support that comparison at the common filtering level. Category and month are present in the budget grain, so those levels can provide meaningful shared context for actual and budget values. An individual product may describe a sales row, but it doesn’t automatically describe a category-level budget row. The fact that a visual permits product names beside a budget value doesn’t establish that the allocation is meaningful.
This also explains why normal-case testing is weak on its own. A grand total might look correct because duplicated values happen to collapse under one calculation or because the chosen filter doesn’t expose the mismatch. A different product, month, delivery condition, or combination of fields can reveal the problem. Testing should challenge the model at the boundaries created by its grain statements.
For each fact table, select a few observations whose meaning can be traced. If the sales grain is one order line, verify that the table preserves that level after query transformations. If the budget grain is category and month, verify that the combination identifies the intended budget observation. Then apply dimension filters at the declared levels and confirm that the relevant fact rows respond as expected.
Report behavior provides another form of evidence. Start from a known total and add descriptive fields one at a time. A lower-level field that the fact can legitimately support should divide the total into meaningful parts. A field at an incompatible level may repeat a value, produce missing combinations, or change a total unexpectedly. Those symptoms don’t identify every cause by themselves, but they tell you where to continue the investigation.
Refresh success is useful operational evidence, but it answers a narrower question. It tells you that Power BI completed the refresh process under the tested condition. It doesn’t prove that a merge preserved grain, that dimension keys represent the intended level, or that report totals remain meaningful. Likewise, access to a report confirms that a consumer can reach it. It doesn’t confirm the correctness of the analytical model.
That distinction prevents a familiar troubleshooting mistake. An analyst notices a wrong total and changes whichever nearby setting is easiest to reach. Perhaps a visual interaction is disabled, a calculation is rewritten, or a query is grouped more aggressively.
Any of those actions can change the displayed number. None is automatically correct. The change must address the layer producing the mismatch while preserving the surrounding requirements.
Grouping the sales data to one row per month might remove duplicated order lines, for example, but it would also discard the product-line detail required by the business. Removing delivery status from the report could hide the problem while also removing requested analysis. A calculation that returns only one repeated amount might suppress duplication in one context and give an incorrect result in another. A technically successful change can still be the wrong solution.
Instead, restate the requirement in business terms. Revenue must represent each sales observation once. Delivery status must describe delivery events at their own grain. Product and date filters must operate at levels supported by the corresponding facts. With those statements in place, inspect query output, model filtering, calculations, and report interaction only as far as each layer controls the observed behavior.
Keep evidence from before and after the correction. Record the expected total for a controlled period, the filter that exposes the issue, and the actual result. After the model is corrected, run the same checks again. Also retest the original unfiltered total so that solving the edge condition doesn’t silently damage the normal case. This is a stronger validation method than concluding that the model is correct because it opens, refreshes, or displays data.
As Power BI requirements combine preparation, modeling, calculations, reporting, and service behavior, grain remains an early architectural decision. Power Query can shape the rows that enter the model. The semantic model determines how reusable dimensions provide context to facts. DAX evaluates results under the resulting filter context.
Reports expose those results through user selections. Service operations distribute and refresh the solution. Each layer matters, but none changes the basic meaning that was assigned to a fact row.
When a total behaves strangely, resist the urge to begin with the most visible layer. Start with the sentence that defines the fact. Check the dimension keys against the intended filtering level. Then follow the data forward until the expected and actual behavior diverge. That path turns an unexplained report number into a testable modeling question.
A trustworthy fact table begins with a precise statement of what one row represents. That grain determines which descriptive dimensions can filter the fact meaningfully and which comparisons would mix incompatible levels of detail. When totals change unexpectedly, don’t treat a successful refresh, relationship, calculation, or visual as proof that the model is correct. Reproduce the failing filter, inspect the query and semantic-model behavior that controls it, and compare the result with the declared grain. If the fact rows retain their intended meaning, the dimension keys operate at the required filtering level, and normal and edge cases produce the expected results, you have verifiable evidence that the design satisfies the business requirement rather than merely appearing to work.
Define the fact-table grain in one sentence and then name a change that would violate it. Which duplicate total or ambiguous row would expose that violation? The companion episode reference keeps the inspection details and verification sequence together for hands-on review. This lesson was produced by Bare Metal Cyber dot com.