A Power BI report is only as dependable as the semantic model behind it. When descriptive tables are kept apart from measurable events, relationships pass filters in the direction you expect, and calculations are defined once rather than rebuilt in every visual, report authors get totals they can trust and slices that mean what they appear to mean. The star schema is the pattern Microsoft’s guidance treats as the usual starting point, but it is a default to reason from, not a rule that fits every source system.
What a good model actually changes for the analyst
Every visual in a Power BI report sends a query to the semantic model. The model decides which tables can be used to filter or group, which tables hold the numbers being summarized, how a filter selected in one table reaches another, and which calculations are available. If those decisions are made clearly, a chart total is predictable and a slicer does what its label promises. If they are not, the same visual can show different numbers depending on which field a author happened to drag in, and the cause is hard to trace.
As an Amazon Associate I earn from qualifying purchases.
Good modelling therefore improves analysis in four concrete ways: it gives readers stable tables to slice by, keeps numeric facts at a consistent level of detail so that sums are meaningful, makes relationships carry filters as intended, and stores business logic in named measures so the same definition appears everywhere.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Dimension and fact tables do different jobs
Most analytical models separate two kinds of table. Microsoft’s star-schema guidance puts the difference in one sentence: “Dimension tables enable filtering and grouping.” Dimension tables describe the things an analysis is about, such as products, customers, stores, or calendar dates. Their columns become slicers, axis labels, and legend values.
#1 Best Overall
The same guidance continues: “Fact tables enable summarization.” Fact tables record observations or events, such as orders, shipments, or payments, together with numeric columns that can be added, averaged, or counted. Fact tables usually hold keys that point to the dimensions and very few descriptive fields.
The distinction is conceptual rather than a setting. Power BI does not mark a table as a dimension or a fact. The roles emerge from the relationships between tables and their cardinality, which is why a table can behave as a dimension in one model and as a fact in another.
State the grain before you write a single measure
The grain is what one row in a fact table represents. A table where each row is one order line has a different grain from a table where each row is one order, and a table of daily stock counts has a different grain again. Microsoft’s guidance recommends keeping fact-table rows at a consistent grain so that a measure such as total quantity sums comparable records.
Recommended Free Tools
Mixing grains is one of the most common ways a model produces numbers that look plausible but are wrong. An order-header amount repeated on every order line will be counted several times once it is summed. Before building relationships, write one sentence per fact table that begins “one row represents…”. If you cannot finish that sentence without listing two different things, split the table.
Rank #2
Relationships are filter paths, not data-cleaning rules
A relationship tells Power BI how a filter applied to one table should propagate to another. The most common form is one-to-many: the dimension side holds unique values for the key, and the fact side may hold the same value many times. Filters flow from the dimension to the fact table, so selecting one product shows only the sales rows for that product.
Microsoft’s relationship documentation is explicit about the limit of this mechanism: “Model relationships don’t enforce data integrity.” A relationship will not remove orphan rows, fix a misspelled key, or reject duplicate keys on the dimension side. Those problems must be found in the source data or in the transformation step. Duplicate values on the one side can cause a refresh to fail, and mismatched column data types or differing time components can make values that look identical fail to match.
Cardinality and filter direction
Cardinality describes how many rows on each side can match. Power BI’s default, a single-direction one-to-many relationship, is the pattern a star schema depends on. Bidirectional filtering can be useful in narrow cases, but it lets filters travel in more than one direction and can create ambiguous paths. Use it deliberately, and test a few visuals afterward to confirm that totals still match what you expect.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchChecks to run when a visual looks wrong
- Confirm that the key on the dimension side has no duplicate values.
- Compare the column data types on both sides of the relationship, including whether one side is a date with a time component.
- Count fact rows that have no matching dimension key, since they will fall outside any slice of that dimension.
- Check that the relationship is active and that the filter direction matches the intended reading of the model.
- Recalculate a total in the source system and compare it with the report total at the same grain.
A worked example: sales analysis
Consider a retailer that wants to analyse sales. A workable starting design has one fact table and three dimensions. The fact table is a sales table in which each row is one order line, and it holds keys, quantity, and amounts. The dimensions are product, customer, and date.
| Table | Role | Typical columns | Grain or key |
|---|---|---|---|
| Sales | Fact | Order line ID, product key, customer key, date key, quantity, net amount | One row per order line, as stated for this example only |
| Product | Dimension | Product name, category, subcategory, brand | One row per product, unique product key |
| Customer | Dimension | Customer name, region, segment | One row per customer, unique customer key |
| Date | Dimension | Date, month, quarter, year, fiscal period | One row per calendar day, unique date key |
With this layout, a visual showing net amount by product category and quarter needs no special logic. The category and quarter come from dimension tables, and the amount is summed from the fact table. The grain statement tells an author that a sum over sales rows counts each order line once, so the same design would be wrong for a source where each row is an order with several lines, unless the amount is first split or the grain is stated differently.
Many-to-many data needs an explicit design choice
Many-to-many situations are common in real data: a customer can belong to several loyalty programmes, and a programme can contain several customers. Microsoft’s many-to-many guidance does not treat these as a reason to connect tables directly and hope for the best.
Dimension to dimension: use a bridge table
When two dimensions relate to each other many-to-many, a bridge table records each association as its own row. The bridge holds one key from each dimension, and each dimension relates to the bridge through a one-to-many relationship. Filters then travel through the bridge in a controlled way. The trade-off is one extra table to maintain and a need to explain the bridge to report authors.
Fact to fact: introduce shared dimensions
Connecting two fact tables directly with a many-to-many relationship is a different problem. Microsoft’s guidance warns that this approach can limit useful filtering and grouping, and that it can behave poorly when data integrity is compromised. In the example its guidance discusses, the recommended approach is to introduce shared dimensions and connect each fact table to them with one-to-many relationships. The same guidance also describes a separate scenario involving facts at a higher grain, so the right answer depends on what each fact table records. Confirm the grain of both facts before choosing a pattern.
Rank #4
Measures carry the business logic
An explicit measure is a DAX expression that is evaluated at query time, in the context of the filters and groupings a visual applies. A measure such as total net sales, written once and used in every report that needs it, centralises the definition. It also prevents a visual from quietly aggregating a column in a way the business does not intend, such as averaging a rate or summing a value that should be counted distinctly.
Name each measure so a report author can tell what it means, and add a description that states what it includes and when it should be used. Descriptive names, useful hierarchies, and hidden implementation fields, which Microsoft’s optimisation guidance also recommends, make the model easier to use correctly. Hiding every numeric column is not automatically better: a column that authors are meant to aggregate directly may be worth leaving visible, while a key or helper column usually is not. Decide per field based on how the report is expected to behave.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choosing a storage mode is a constraint decision
Power BI offers Import, DirectQuery, and Composite storage modes. Microsoft’s optimisation guidance treats them as options to be matched to requirements, not as a ranking. The factors to weigh are listed below.
- Freshness: how current the data must be, and how often it can realistically be refreshed.
- Query performance: how quickly visuals must respond, and how much work the source can absorb at query time.
- Source location and capabilities: where the data lives and what the source system can do.
- Data volume: how many rows the model must hold or query.
- Operational complexity: how much refresh scheduling, source connectivity, and maintenance the team can support.
A star schema is compatible with each mode, so the modelling decisions above still apply. The storage choice changes where the work happens and how fresh the answers are, not whether the model needs clear dimensions, grains, and relationships.
Where the star schema is a default rather than a rule
Microsoft’s own star-schema guidance acknowledges that the optimal design involves judgment and can sometimes depart from the general pattern. A simple star is easy to explain and usually gives clear filter behaviour and correct aggregation. More complex relationship patterns can be justified when the source data is shaped that way, when reporting needs cannot be met otherwise, or when the model’s size and performance requirements call for it. Each departure should be made for a stated reason and tested against the same questions: whether filters are clear, whether aggregations stay correct, whether integrity problems are visible, whether report authors can use the model without help, and whether size and performance remain acceptable.
Where to go next
If you are new to dimensional modelling, begin with the star-schema guidance and the relationship documentation linked above, then build a small model from one fact table and two or three dimensions. Microsoft’s intermediate learning module Design semantic models for scale in Microsoft Fabric covers storage-mode selection, star-schema relationships, scalable calculations, and settings for scale. It lists prior understanding of data modelling concepts and experience with Fabric and Power BI as prerequisites, so it suits readers who already know the basics.
For a deeper treatment of dimensional design, Microsoft names The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, third edition (2013), by Ralph Kimball and others, as further reading. It covers dimensional design in general rather than Power BI specifically.
The model is the part of a Power BI project that outlasts individual reports. Spending time on grain, relationships, and named measures before building visuals is usually the cheapest way to make later analysis consistent.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
