Why a star schema beats one flat table
A flat table copies ProductName and Category onto every one of the 42,000 sales rows. That wastes memory and lets the same product carry two different category spellings, which splits your totals. A star schema stores the product once in the Products dimension and points to it with ProductKey, so every sale agrees on the category.
The shape also makes DAX predictable. With one fact table in the middle, filter context flows outward from dimensions to the fact and your measures behave the same on every page. You stop fighting unexpected blanks and double counts.
Build the central fact table
Start with the Sales table as your fact. It represents one order line and holds numeric columns such as Amount, Quantity, and DiscountPct plus foreign keys like ProductKey and CustomerKey.
Move descriptive text out of the fact. ProductName belongs in Products, not in Sales. The fact should be mostly keys and additive numbers.
In Power Query, set Amount to Decimal Number and OrderDate to Date. Wrong types break SUM and time intelligence later, and you will chase blanks for an hour.
Call it Sales, not Sheet1 or Query1. A clear fact name makes every measure read naturally, such as SUM(Sales[Amount]).
Build the dimension tables
The Products dimension holds one row per product: ProductKey, ProductName, Category, and Price. It connects to Sales on ProductKey with a one-to-many relationship.
Build a Calendar table with one row per date: Date, Year, Quarter, Month. Mark it as a date table so time intelligence functions recognize it.
The Customers dimension holds CustomerKey, Region, and Segment. Slice Sales by Region through this link instead of storing Region on every sales row.
I recommend giving every dimension a single-column key such as ProductKey rather than joining on a text name. Integer keys make relationships faster and avoid spelling mismatches that silently drop rows.
Set relationships the right way
Click the Model icon and drag ProductKey from Products to ProductKey in Sales. Power BI creates a one-to-many relationship from the one side (Products) to the many side (Sales).
Keep the default single direction from dimension to fact. Products filters Sales, not the reverse, which is what star schemas expect.
Right-click Calendar, choose Mark as Date Table, and select the Date column. Without this, SAMEPERIODLASTYEAR and YTD measures return blank.
Mistakes that break a model
A common mistake is linking two fact tables directly, such as Sales to Orders, instead of through a shared dimension. That creates ambiguous filter paths and totals that double count.
This will break if you set both tables to filter each other on a large model. Bidirectional filters cause ambiguous paths and slow refreshes. Use them only with a clear reason.
If Products has two rows with the same ProductKey, the one-to-many relationship becomes many-to-many and your totals splinter. De-duplicate the key in Power Query before loading.
Do not put daily and monthly rows in the same fact table. Different grains double count. Split them into separate fact tables that share dimensions.
Worked example: a clean two-table model
Sales has 42,000 rows with ProductKey and Amount. Products has 300 rows with ProductKey, Category, and Price. After linking ProductKey, drop Total Sales = SUM(Sales[Amount]) on a bar chart by Category. Electronics shows 512,400, Office shows 388,900, and Home shows 343,900, summing to the 1,245,300 grand total. No category appears twice because the key is unique.
If you know Excel, here is the shift
In Excel you often paste everything into one tab and build a pivot from it. Power BI asks you to separate the lookup tables first. Our Excel for data analysis guide covers pivots; the modeling step here is what turns those pivots into a refreshable, multi-page model with consistent totals.
The payoff is governance. When Category lives in one Products table, changing a label updates every report at once. A flat Excel dump forces you to find and fix the typo in fifty places.
When a snowflake makes sense
A pure star schema keeps every dimension flat, but real data sometimes nests. If Products has a CategoryKey that points to a Categories table, you have a snowflake: a dimension joined to another dimension. Power BI handles this, but each extra hop adds a join the engine must resolve, so keep snowflakes shallow and only where the source truly splits.
In the sample model, Products links to Categories on CategoryKey. A bar chart of Sales by Category still works because the filter flows Products to Sales and Categories to Products. The totals stay correct at 1,245,300 as long as every key is unique on the one side of each relationship.
Drag CategoryKey from Categories to CategoryKey in Products. Set the relationship to single direction from Categories to Products so the filter flows toward the fact.
Drop Category from Categories on the axis of a Total Sales bar chart. Electronics, Office, and Home still sum to 1,245,300, proving the snowflake did not break the totals.
Handling role-playing dimensions
A single date table often serves several roles: Order Date, Ship Date, and Due Date all live in the Calendar table. Power BI cannot link one table to Sales three times with the same key, so you create role-playing by adding multiple relationships and activating one at a time, or by building shadow date tables for each role.
The clean approach is a separate Calendar table per role, each marked as a date table and related to the matching column in Sales. This keeps time intelligence working for Ship Date without confusing the engine about which date you meant in a measure.
Reference the Calendar query and rename it Calendar Ship, then relate Calendar Ship[Date] to Sales[ShipDate]. Mark it as a date table so YTD by ship date works.
Ship YTD = CALCULATE(SUM(Sales[Amount]), DATESYTD('Calendar Ship'[Date])). This reads the ship calendar while Total Sales reads the order calendar, with no conflict.
I recommend naming each role-playing table clearly, such as Calendar Order and Calendar Ship, because a single ambiguous Date table is the most common cause of time intelligence returning blank on one of the roles.
A pre-publish modeling checklist
Before you publish, walk a short checklist so the model behaves for every viewer. Confirm each dimension has a single unique key, each relationship filters from dimension to fact, the date table is marked, and no two fact tables link directly. These four checks prevent almost every total-related bug reported after go-live.
Also verify the model size in the Performance Analyzer. A star schema with 42,000 sales rows and 300 products should sit well under a few megabytes; if it is tens of megabytes, you likely left text columns in the fact or duplicated a dimension by accident.
In Power Query, right-click ProductKey in Products and choose Remove Duplicates, then confirm the row count stays 300. Any drop means duplicates that would corrupt the relationship.
In Model view, check that every arrow points from the small dimension to the large fact. A reversed arrow is a sign the filter will not reach the numbers you expect.
Fixing a slow or bloated model
If a model refreshes slowly or the file is larger than expected, the cause is almost always shape, not size. A fact table carrying long text columns, or a dimension joined on a text key, forces Power BI to store and compress far more than necessary. Trim the fact to keys and numbers, and the model shrinks without losing a single report.
In Power Query, remove descriptive text from Sales and keep only keys like ProductKey and numeric columns like Amount. The 42,000 rows compress to a fraction of their previous size.
Replace text relationships with integer keys such as ProductKey. Integer joins compress better and avoid the case-mismatch errors that drop rows from a total.
Another silent killer is a bidirectional relationship left on by default. Unless you have a clear reason, keep every relationship single direction from dimension to fact. Bidirectional filters make the engine evaluate both directions and can turn a fast model into a slow one on large data.
Documenting the model for your team
A model is a shared asset, so describe each table and the meaning of key columns. Power BI lets you add a description to a table and a column, and those notes appear in the Fields pane when a teammate hovers. A one-line note on Sales[Amount] stating 'gross revenue, excludes tax' prevents a hundred future arguments.
Right-click Amount, open Properties, and write 'Sum of order line revenue before discount'. The note travels with the model to every report built on it.
In Model view, label each relationship with its key pair, such as Products[ProductKey] to Sales[ProductKey], so a new analyst understands the filter flow without reverse-engineering it.
I recommend writing a one-page data dictionary alongside the .pbix, listing each table, its grain, and its key. When the model moves to a teammate, that page is worth more than any comment inside the file.
One model, many reports
A well-shaped star schema pays off the moment a second report appears. Because the Sales fact and the Products and Calendar dimensions are clean and related, a new dashboard reuses the same Total Sales measure without rebuilding the logic. The model becomes a shared foundation, and each new page is a different view of the same trusted numbers.
This is the real reason to invest in modeling early. A flat table might be quicker for the first chart, but by the third report the duplicated logic drifts and totals disagree. A star schema keeps one definition of sales, one list of products, and one calendar, so every report agrees by construction rather than by constant manual fixes.
Build Total Sales = SUM(Sales[Amount]) once in the model, then drop it on the sales, finance, and operations reports without rewriting it.
Slice any report by the same Products[Category] or Calendar[Month], because the relationships already point every fact row at the right dimension member.
I recommend treating the model as a product with its own owner, so changes go through review and no single report quietly forks the definition of a key metric.
Signs your model is ready
You know the model is sound when a new analyst can build a correct chart in minutes. If they have to write a workaround to get a total, the model is failing them. The signals of readiness are simple: every total reconciles, every slicer filters as expected, and no measure needs a paragraph of explanation to use.
Sum Sales[Amount] with no filters and confirm 1,245,300 matches the source export, then confirm each Region slices to its known subtotal.
Ask a teammate to build a Category-by-Month chart from scratch. If they succeed without asking, the model is intuitive and ready to share.
Key takeaways
A star schema is the single decision that makes every later step easier: one fact table in the center, small dimensions around it, and relationships that flow from dimension to fact. The payoff is consistency, because every total traces back to the same Sales table and the same Products and Calendar dimensions, so reports stop disagreeing.
Spend the time to get keys unique, types correct, and directions single, because those three choices prevent nearly every total-related bug. A model that passes the reconcile test and the new-user test is ready to support many reports, not just the first chart you built it for.
Next steps
Load a small sample, build one fact and two dimensions, and confirm your measures slice correctly. Add CALCULATE once the relationships are solid, since filter context depends entirely on this structure. The Power BI DAX CALCULATE guide shows how filters travel along these relationships, and the PL-300 guide lists star schema as a core exam area.
If you are choosing tools, the Power BI vs Tableau comparison explains how both handle modeling so you can commit to one workflow.
