Power BI star schema mistakes to avoid

The most expensive mistake is a fact table that tries to hold more than one event. An order header carries delivery charges once per order; an order line carries quantity and price once per product. Put both in one table and the delivery charge repeats on every line, so any sum of it overstates the total.

The fix is a fact table per grain, related through conformed dimensions. A header fact and a line fact can share Customer, Date, and Channel. Report header measures from the header fact and line measures from the line fact. Do not combine them in a single visual without a defined allocation rule.

A simple illustration: an order with three lines and a delivery charge of 20 recorded on each line contributes 60 to any total of that column, not 20. The error survives every visual that sums the fact table, so it is rarely caught by checking a single chart.

A single import of a transaction extract with fifty columns feels simple until every visual scans the same wide rows. A star schema separates numeric events from descriptive attributes, which compresses better and gives each dimension one place to change.

The related mistake is snowflaking everything. Product to Subcategory to Category is sometimes justified, but each extra table adds a relationship and a filter path. Flatten small hierarchies into one dimension unless the shared table is large and reused. Keep the keys and hide them from report view.

Relationships decide how filters travel. Most of them should be one-to-many, from the dimension to the fact, with a single filter direction. The exceptions should be deliberate and documented.

Test each relationship by filtering a dimension and counting fact rows. If a filter changes a total in a way that cannot be explained, inspect the direction and the key before changing any DAX. Ambiguity is a model problem, and a measure workaround usually hides it.

Time intelligence functions expect a continuous date table marked as such. Using the date column in the fact table creates gaps on days with no sales, and year-to-date or same-period-last-year calculations can quietly return wrong results. Build a separate date table with one row per day, mark it, and relate it to each date role.

Multiple date roles need a decision: separate role-playing date tables for clarity, or one table with inactive relationships and USERELATIONSHIP in the measures that need them. Power BI allows only one active relationship between two tables; any additional relationship is created inactive and does nothing until a measure activates it.

Users should see dimensions and measures, not foreign keys, technical column names, or a fact table with a hundred fields. Hide keys, format measures consistently, and put related measures in display folders. A clean field list is part of the model, not decoration.

Check data types and column quality before publishing. A date stored as text, a numeric key stored as text, or a column with mixed types will cause relationship failures and slow queries. Fix them upstream or in Power Query, and remove columns no visual uses.

Symptoms in the report usually point back to a small number of model problems. Use the table as a diagnostic starting point, then confirm at the lowest grain.