Star Schema in Power BI for Beginners: Fact Tables, Dimensions and Relationships

Gulab Chand Tejwani
PMP · PL-300 · Microsoft Certified Trainer
· 11 min read

A star schema is a way of organising data into one central fact table, which holds the events you measure (sales, orders, transactions), surrounded by dimension tables, which describe those events (date, product, customer, branch). Each dimension connects to the fact table with a one-to-many relationship, so the diagram looks like a star. Power BI is designed to work best with this shape: filters flow cleanly from dimensions to facts, DAX measures stay simple, and the model is smaller and faster than one big flat table.
I'm Gulab Chand Tejwani, a Microsoft Certified Trainer and PL-300 certified Power BI analyst at TechNet Consultancy in Anand. If I could make every beginner learn one modelling idea before touching a chart, it would be this one. Most of the confusing totals, slow reports and "why won't this slicer filter that visual?" questions I see come from skipping it.
What is a star schema in simple words?
Think of a sales invoice. Some information is the event itself: on this date, this customer bought this many units of this product for this amount. Other information describes the things involved: the product's category and brand, the customer's city, the date's month and financial year.
A star schema separates the two:
- The fact table has one row per event, with numeric columns you add up (quantity, amount, cost) and key columns that point to the dimensions (DateKey, ProductKey, CustomerKey).
- The dimension tables have one row per thing, with descriptive columns you filter and group by (Category, Brand, City, Month, Financial Year).

Microsoft's own guidance, Understand star schema and the importance for Power BI, is worth reading in full once you've built your first model. It's one of the clearest documents Microsoft has written on modelling.
| Fact table | Dimension table | |
|---|---|---|
| One row per | Event (invoice line, transaction) | Thing (product, customer, day) |
| Typical columns | Keys plus numbers to add up | Key plus descriptive text |
| Row count | Large, keeps growing | Smaller, fairly stable |
| Used in visuals for | Measures (sum, count, average) | Rows, axes, slicers, filters |
| Relationship side | Many | One |
Why does Power BI prefer a star schema over one flat table?
Many people start by loading one wide Excel sheet with everything in it. It works for a while. Then problems start.

Filtering gets unreliable. With a star schema, a slicer on Product[Category] filters every fact table related to Product. With several flat tables, each one has its own copy of "Category", and a slicer on one doesn't filter the others.
DAX gets harder. Measures like sales versus target, or this year versus last year, need clean dimension tables, especially a proper date table. Time intelligence functions expect one.
The model gets bigger and slower. Power BI compresses columns. A text column like "Category" repeated across two million sales rows compresses less well than a small Product table with a few hundred rows and a whole-number key in the fact table.
Combining facts becomes impossible. Sales are recorded per invoice line, targets per month per branch. Two fact tables with different grain can only be compared through shared dimensions (Date and Branch). That's the star schema doing its job.
What is the grain of a fact table?
The grain is what one row of the fact table means. "One row per invoice line" is a grain. "One row per branch per month" is another. Write the grain down before you build, because everything else follows from it.
Getting the grain wrong causes double counting. If you store an invoice-level discount on every invoice line, summing the discount column multiplies it by the number of lines. The fix is either to allocate the discount across lines, or to keep invoice-level values in a separate fact table with an invoice grain.
The grain also decides which dimensions a fact can connect to. A monthly targets table can relate to the Date table only if you have a column at month level, or if you relate it to a first-of-month date. That's a common situation, so here's how I handle it.
How do you compare sales with targets?
Say Sales has one row per invoice line with a date, and Targets has one row per branch per month. Both tables can share the Branch dimension and the Date dimension:
- In Targets, store the month as a real date: the first day of the month, such as 1 April 2026.
- Relate Targets[MonthStart] to Date[Date]. The relationship works because 1 April exists in the Date table.
- Relate both Sales and Targets to Branch on BranchCode.
- Write two simple measures and compare them.
Total Sales = SUM ( Sales[Amount] )
Total Target = SUM ( Targets[TargetAmount] )
Achievement % = DIVIDE ( [Total Sales], [Total Target] )
Put Date[Month] and Branch[BranchName] in a matrix with all three measures, and it works at month level and above. At day level the target shows only on the first of the month, which is why you'd keep that visual at month grain or higher.
How do fact and dimension tables relate?
Each relationship runs from a dimension (the "one" side) to the fact table (the "many" side). One product appears once in Product but many times in Sales.
Three settings matter in Model view:
- Cardinality. Should almost always be one-to-many from dimension to fact. If Power BI suggests many-to-many, it usually means your dimension has duplicate keys. Fix the data rather than accepting it.
- Cross-filter direction. Keep it Single, from dimension to fact. "Both" looks convenient but can create ambiguous filter paths and slow models. Use it only when you understand exactly why you need it.
- Active versus inactive. Only one active relationship can exist between two tables. If Sales has both OrderDate and ShipDate, one relationship to Date is active and the other is inactive. You activate it inside a measure with USERELATIONSHIP. Microsoft explains this in its guidance on active versus inactive relationships.
PL-300 tests all three under "Design and implement a data model", along with role-playing dimensions, which is the OrderDate and ShipDate case. My PL-300 exam guide has the full list, and DAX CALCULATE explained with examples shows the USERELATIONSHIP measure.
Step by step: turning a flat export into a star schema
Here's the walk-through I do in class with a typical flat sales export that has these columns: InvoiceNo, InvoiceDate, CustomerName, City, State, ProductCode, ProductName, Category, Brand, Qty, Rate, Amount.

Step 1: Load it once as a staging query. In Power Query, connect to the file, do the basic cleaning (types, trimming, removing total rows), and name the query "Sales Staging". Right-click and untick Enable Load. My post on Power Query data cleaning steps covers that cleaning in detail.
Step 2: Build the Product dimension. Reference the staging query. Keep ProductCode, ProductName, Category and Brand. Remove Duplicates on ProductCode. Name it "Product".
Step 3: Build the Customer dimension. Reference staging again. Keep CustomerName, City and State. Remove duplicates. If there's no customer code, add an Index column to create a CustomerKey and merge it back into the fact table, or use the name as the key if it's truly unique.
Step 4: Build the Sales fact table. Reference staging. Keep InvoiceNo, InvoiceDate, the keys (ProductCode, CustomerKey), Qty, Rate and Amount. Remove the descriptive columns that now live in dimensions.
Step 5: Add a Date table. Don't rely on the automatic hidden date tables. Create a dedicated Date table that covers every date in your data, with Year, Month, Month Number, Quarter and, for Indian businesses, Financial Year starting in April. Mark it as a date table. I'll cover this properly in the next post on DAX time intelligence.
Step 6: Create relationships. In Model view, drag Product[ProductCode] to Sales[ProductCode], Customer[CustomerKey] to Sales[CustomerKey], and Date[Date] to Sales[InvoiceDate]. Check each is one-to-many, single direction.
Step 7: Hide the keys. Hide the key columns in the fact table from report view, so report builders use Product[Category], not Sales[ProductCode]. Hide numeric fact columns too once you've created measures for them, so everyone uses the same Total Sales measure.
Now drop Category from Product, Month from Date and a Total Sales measure into a matrix. It just works, and so will every visual after it.
What mistakes do beginners make with a star schema?
- Duplicate keys in a dimension. "P001" appears twice with slightly different names. Power BI then refuses a one-to-many relationship. Find duplicates with Keep Duplicates in Power Query and fix the source.
- Facts without a matching dimension row. Sales has product codes missing from the product master, so those sales show under "(Blank)". A left anti merge in Power Query lists them.
- Snowflaking everything. Splitting Product into Product, Subcategory and Category tables is allowed, but in Power BI it's usually simpler to flatten them into one Product dimension.
- Using bi-directional filters to fix a bad design. If you need "Both" everywhere, the model shape is usually wrong.
- Measures in the wrong place. Create a dedicated empty table for measures, or keep them in the fact table. Don't scatter them across dimensions.
How do you know your model is good?
A simple test I give students: can a colleague who has never seen your file build a correct report by dragging fields, without asking you anything? If they can find Category in Product, Month in Date, and a Total Sales measure, and every total matches, the model is good.
Then run Performance Analyzer on your report. With a proper star schema, most visuals return quickly even on a laptop. If you're still deciding whether a report needs a model at all, my Power BI vs Excel comparison has four quick questions to settle it.
Where to learn this properly
In our instructor-led Power BI training in Anand and live online, data modelling and DAX get 10 of the 30 teaching hours, and you build star schemas from messy files with a trainer reviewing your relationships. For self-study, the Power BI online course has modelling lessons and labs with sample datasets.
FAQ
What is the difference between a fact table and a dimension table?
A fact table records events with numbers you aggregate, such as sales amount and quantity, one row per transaction. A dimension table describes things, such as products or customers, one row per item, with columns you filter and group by.
Is a star schema required in Power BI?
It's not enforced, but Microsoft recommends it, and Power BI's filtering, DAX and compression work best with it. Most modelling problems in Power BI get easier once the data is in star shape.
What is a snowflake schema?
A snowflake schema splits dimensions into further related tables, such as Product linked to Subcategory linked to Category. Power BI supports it, but flattening those into one dimension usually makes the model simpler to use.
Should relationships be single or both directions?
Use single direction, from dimension to fact, by default. Bi-directional filtering is sometimes needed for specific cases but can cause ambiguity and slower performance.
How many fact tables can a Power BI model have?
As many as you need. Sales, targets and inventory can each be separate fact tables, connected through shared dimensions such as Date, Product and Branch.