Star Schema in Power BI: Why One Flat Table Cannot Answer the Question

1. Fact or dimension, and how the model decides

Before the definitions: you have an orders table and a products table. Say which one you would count and which one you would slice by, before reading on. You already know the answer; the words are what this section adds.

Microsoft's guidance splits every model table in two, and the split is by job.

Dimension tables describe things. Products, people, places, dates. They have a key column that uniquely identifies each row, plus columns you want to filter and group by. They are usually small. Ours has three rows.

Fact tables record events. Orders, transactions, readings, page views. They have key columns pointing at the dimensions, plus the numbers you add up. They are usually large and they keep growing. Ours has sixteen rows and would have sixteen thousand in a year.

Two sentences from the guidance are worth keeping, because they turn the distinction from a vibe into a rule: "Dimension tables enable filtering and grouping" and "Fact tables enable summarization."

Here is the part that surprises people. There is no setting anywhere in Power BI that declares a table to be one or the other. The guidance says so directly: "There's no table property that modelers set to set the table type as dimension or fact. It's in fact determined by the model relationships." Specifically the cardinality: "The 'one' side is always a dimension table while the 'many' side is always a fact table."

So the type is not a label you apply. It is a consequence of how you joined things, which means getting the relationships right is getting the model right.

2. The flat table, and the question it cannot answer

Before the problem: here is the flat table, one row per order with everything on it. Look for what you would need to compute gross margin by product, and notice what is not there.

There is no cost anywhere. Revenue is on every row and cost is on none of them, so a margin measure has nothing to work with. That is not a flaw in this particular export; it is the normal state of a fact table. Costs belong to products, not to orders, and repeating a product's cost on every one of its order rows is exactly the duplication that a separate table exists to avoid.

Here is the answer once a products table is in the model, with a cost column.

Check a line by hand: 23 desks at a cost of 140 each is 3,220, and 5,060 minus 3,220 is 1,840. And now there is a claim instead of a list. Lamps are last by revenue and first by margin. A revenue ranking and a margin ranking put them at opposite ends, and the only thing standing between those two sentences is a second table and a relationship.

Say out loud what a flat table would have to do to answer this. It would have to carry the cost on all sixteen rows, repeated. Then a cost correction becomes sixteen edits instead of one, and a product that sold nothing this month disappears from the model entirely.

3. Splitting it: what goes where

Before the split: take the eight columns of the flat table and sort them into "describes something" and "is an event or a number". Do it before reading on.

Here is where they land.

Two decisions in that table are worth explaining because they are the ones people get wrong.

Region moved to the Reps table. In our data each rep works one region, so region describes the rep, not the order. Putting it on the fact table would repeat it sixteen times and make it possible for the same rep to appear in two regions on different rows, which is a data quality problem you have just invented. If reps could move between regions over time, that is a slowly changing dimension and it needs versioning, which is a different and harder problem.

Revenue stayed on the fact table even though it is Units times UnitPrice. It is a number you add up, so it is a fact. And it is deliberately stored rather than looked up, because the price on the day of the order is not necessarily today's list price. A fact table records what happened; a dimension records what is currently true.

The date dimension is the one nobody regrets. Even on five months of data it is what makes month-over-month, year-to-date and prior-period measures work at all, for reasons worked through in DAX time intelligence.

4. Building the relationships, and the direction that matters

Before the settings: you join Products to Orders on the product name. Predict which way filters should travel, before reading on.

From Products into Orders, and only that way. Choosing "Products" in a slicer should cut down the orders. Choosing an order should not cut down the product list. Each relationship gets three settings, and the defaults are usually right.

The setting worth resisting is Both for the cross-filter direction. It looks harmless and it makes filters travel in both directions, which means a filter can leave a dimension, reach the fact table, and travel back up a different relationship into a second dimension. With three dimensions there are paths the engine has to resolve, and with more than a few there are ambiguous ones it will refuse. Turn it on only for a specific, tested reason.

Then, in the visuals, slice by the dimension's columns rather than the fact table's. Both work in our model because Product is on both tables, and they behave differently the moment a product exists with no orders. Slicing by Products[Product] shows it with a blank measure; slicing by Orders[Product] never mentions it. One of those is a report that can say "this line sold nothing", and the other is a report that quietly loses it.

My own tidying rule: once the relationships exist, hide the fact table's key columns from report view. Nobody should be choosing between Orders[Product] and Products[Product] at report-building time, and hiding one removes the choice.

5. Snowflake: when a dimension chains off another

Before the shape: our products have categories, Furniture and Lighting. You could store categories in their own table joined to Products. Say what that would buy and what it would cost.

That shape is a snowflake: a dimension normalized across several tables so the chain runs Category, Products, Orders instead of Products, Orders. Microsoft's guidance is direct about the trade: "Generally, the benefits of a single model table outweigh the benefits of multiple model tables", and it lists four specific costs.

• More tables loaded, "which is less efficient from storage and performance perspectives".
• "Longer relationship filter propagation chains need to be traversed", so filters take an extra hop.
• More tables in the Data pane, "which can result in a less intuitive experience", especially when the extra table has one or two columns.
• "It's not possible to create a hierarchy that comprises columns from more than one table", so a Category to Product drill-down cannot exist.

That last one is the practical killer. With Category on the Products table you can build a hierarchy and let users drill from Furniture down to Desk in one visual. With Category in its own table you cannot, at all.

So flatten it: put Category on the Products table as a column, which is exactly how our three-row table is built. The cost is repeating the word "Furniture" twice, which on a three-row table is nothing and on a three-thousand-row product table is still nothing.

The check, on our data: Furniture is 8,290 of revenue and Lighting is 1,600, and they add to 9,890. That grouping works because Category sits on the dimension the fact table is joined to, one hop away.

6. Two dates on one table: role-playing dimensions

Before the problem: orders have an order date and a ship date, and you want to analyse by either. Predict what happens if you join the date table to both columns.

Power BI creates both relationships and makes only one active. The guidance states the constraint plainly: "there can only be one active relationship between two Power BI semantic model tables. All remaining relationships must be set to inactive." A dimension used in several roles like this is called a role-playing dimension.

Two ways to handle it, and the second is the one Microsoft recommends.

Activate the inactive relationship inside specific measures. USERELATIONSHIP turns one on for the duration of one CALCULATE.

Revenue by Ship Date =
CALCULATE (
    [Total Revenue],
    USERELATIONSHIP ( 'Date'[Date], Orders[ShipDate] )
)

It works and it does not scale. You need a duplicate of every measure per date role, the Data pane fills with near-identical names, and, as the guidance notes, you still "can't produce a visual that plots order date sales by shipped sales", because only one path can be active at a time.

Build a second date table. A Ship Date table, cloned from the first, each with its own active relationship. One line of DAX creates it: Ship Date = 'Date'. Then rename its columns to be self-describing, Ship Year rather than Year, so a chart title reads correctly without editing. The cost is a duplicated dimension table, and since dimension tables are small, that is rarely a concern.

7. The exceptions Microsoft actually recommends

Before the exceptions: the rule so far is "never mix facts and dimensions in one table". Guess whether there is a documented exception, before reading on.

There is exactly one common one, and knowing it stops you from over-normalizing.

Degenerate dimensions. An order number is an attribute you want to filter by, and building a one-column table just to hold it would add a table and clutter the Data pane for no benefit. The guidance calls this "an exception to the formerly introduced rule that you shouldn't mix table types" and says to leave it on the fact table. Our OrderID is exactly this.

Three more shapes worth recognizing when you meet them, all documented, none needed on our sixteen rows:

Surrogate keys. When a dimension has no single unique column, add one. In Power Query that is an index column, merged into the fact query so both sides carry it.

Junk dimensions. Several tiny attributes with few values each, like order status and delivery status, consolidated into one dimension table rather than several, to reduce clutter and model size.

Factless fact tables. A table of only dimension keys, recording that something happened, or bridging two dimensions in a many-to-many relationship. If a salesperson can belong to several regions, that bridging table is the recommended way to model it.

Now picture your own model's table list. Which tables are there because something genuinely has its own grain, and which are there because a source system happened to export them separately?

Edge cases that catch people out

Six that each cost somebody an afternoon.

Duplicate keys on the dimension side. Power BI refuses to create a one-to-many relationship if the "one" side is not unique, and the error message names the constraint rather than the offending row. Find the duplicate with a quick group-by before assuming the relationship is impossible.

Blank keys on the fact side. Orders whose product is missing get lumped into a blank row on the dimension. The total still reconciles, and the blank line in a visual is the model telling you about a data quality problem, not a bug to hide.

Both-direction filtering everywhere. It solves one visual and creates ambiguity for the engine. Microsoft's own filter modifier list includes CROSSFILTER for changing direction inside a single measure, which is nearly always the better tool than changing it on the relationship.

Mixed grain in one fact table. Order lines and order headers in the same table double-count anything summed. The guidance is explicit that "fact tables always load data at a consistent grain". One row means one thing, and the same thing, throughout.

Slicing by the fact table's copy of a column. Works until a dimension member has no facts, then silently disappears from the slicer. Hide the fact table's descriptive columns to remove the choice.

Modelling one flat export because it is one flat export. The shape of the file you were sent is a fact about the sender's system, not about your model. Power Query exists to reshape it, and Power Query is the same tool doing the same job in Excel.