Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesiTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A Power BI model turns source data into a semantic layer that report visuals can filter, group, and summarize. For most analytical reports, start with a star schema: dimensions such as Date and Product provide context, while fact tables hold the events or values to analyze. The crucial first decision is each fact table’s grain—the precise meaning of one row.
What a Power BI data model does
A semantic model gives report authors a structured way to query an analytical subject. When someone builds a visual, Power BI generates queries that filter, group, and summarize model data. Tables and relationships determine how those operations reach the data; measures define calculations. Microsoft recommends applying star-schema principles to produce a model with dimension and fact tables. Microsoft’s relationship guidance explains how filters move through that model.
Star schema is a strong starting point, not a rule that solves every design problem. The right design depends on what the report must answer, the detail available in the source, and how users need to explore the results. Microsoft describes optimal model design as part science and part art. Its star-schema guidance provides the fuller design discussion.
Facts, dimensions, and the grain of a row
Dimensions describe the context
A dimension describes an entity used to filter or group analysis: for example, a product, person, location, or date. It typically has a key that uniquely identifies each row, alongside descriptive columns such as product name, category, or city. A Date dimension, for instance, can let a report group results by month, quarter, or year.
#1 Best Overall
Facts record what happened or what was measured
A fact table records observations or events, such as sales orders, stock balances, exchange rates, or temperatures. It usually includes keys that refer to dimensions and values that can be analyzed. The fact table should be understood in terms of what one row represents—not just what its columns are called.
State the grain before building relationships
The grain is the level of detail represented by each fact row, determined by the key values present. A sales table might contain one row per product on each order line, while a target table might contain one row per product per month. Those tables do not share a grain, even if both have date and product fields. Keep each fact table’s grain consistent, and describe it plainly before using its values in a report.
Rank #2
Grain matters especially when facts are compared. If sales are recorded by day and product but targets only by year and category, displaying the annual category target beside daily product sales does not make the target a daily or product-level value. Higher-grain facts need measures designed to control how their values are summarized at finer levels. Microsoft’s many-to-many guidance covers higher-grain fact scenarios.
How relationships control filtering
In the common one-to-many relationship, a dimension’s unique row is on the “one” side and matching rows in a fact table are on the “many” side. Selecting a product in a visual can then filter the related sales rows. Relationships propagate filters along model paths; they do not repair missing or duplicate keys in the source data. Check key uniqueness and referential integrity rather than treating a relationship as a data-quality fix.
Single-direction filtering is a sensible default for straightforward dimension-to-fact paths. Bidirectional filtering can be appropriate in some designs, but adding it casually can create ambiguous propagation paths or hurt performance. Confirm that a filter travels the path you intend and that results remain correct when users combine slicers.
How to build a useful model
- Start with report questions. Identify the business process to analyze, the decisions the report should support, and the detail represented by each fact row.
- Shape sources into facts and dimensions. If data arrives as a denormalized export, Power Query can split and prepare it. For large data volumes or advanced warehouse patterns such as slowly changing dimensions, consider doing the preparation in a data warehouse and ETL process before loading the semantic model. Microsoft’s star-schema guidance discusses these trade-offs.
- Relate dimensions to facts. Use a unique dimension key and, normally, a one-to-many relationship to the fact. If a dimension has no single unique column, a surrogate key may be needed; Power Query can add an index column for that purpose.
- Make the model legible to report authors. Hide technical key columns from report view when they are needed only for relationships. Use meaningful table and column names, useful hierarchies, and explicit measures where they improve navigation or govern calculations. A Microsoft Desktop tutorial demonstrates these usability practices.
- Validate both filters and results. Compare totals with known data, test combinations of dimensions, and investigate unexpected blanks or duplicated values. Relationships move filters; they do not validate the underlying keys.
When many-to-many relationships need care
Two dimensions with many-to-many associations
Two dimensions may have multiple associations in both directions—for example, salespeople assigned to multiple regions. Microsoft advises against making a direct many-to-many relationship between dimension tables the default. A bridge table can represent the associations; a factless fact table is one common form of bridge. This makes the relationship structure explicit and supports filtering through the association. See Microsoft’s many-to-many relationship guidance.
Rank #4
Two fact tables
Directly connecting two fact tables with a many-to-many relationship is generally not recommended. Instead, use shared dimensions—such as Date, Product, or Customer—and relate each dimension to each relevant fact with one-to-many relationships. This gives users more flexible ways to filter and group, while reducing the chance that integrity problems are hidden by the relationship design.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Facts recorded at different levels of detail
Shared dimensions do not make two facts’ grains interchangeable. A year-and-category target cannot automatically be interpreted at day-and-product grain. Use measure logic that returns the target only at levels where its meaning is valid, or otherwise aggregates it deliberately. Do not let a visual’s finer-grained rows imply precision the source does not contain.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Active and inactive date relationships
A fact table may contain several date roles, such as order date, due date, and ship date. Although each can relate to the same Date dimension, only one relationship between those tables can be active at a time. The active relationship propagates filters by default; an inactive relationship is used only when a DAX expression activates it.
For example, a model can use Order Date as the default path and calculate a due-date result with USERELATIONSHIP inside a measure. This lets one Date dimension support alternate date analyses without making every date role filter simultaneously. Microsoft’s active vs. inactive relationship guidance and dimensional-model tutorial explain the pattern.
Measures: when to define a calculation
An explicit measure is a DAX formula that returns a scalar result when queried. Measures are useful for encoding a business definition—such as net sales or a carefully scoped target—and applying it consistently across visuals. They also give model owners control over how a value is summarized.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Power BI can also aggregate a numeric column directly in a visual, creating an implicit measure. That can be convenient for simple exploration; not every column needs a separately authored measure. Prefer explicit measures when a calculation has a defined business meaning, needs controlled behavior, or should be reused consistently by report authors. Microsoft’s star-schema documentation discusses measures and model design.
Checks before sharing a report
- Can you state what one row in every fact table represents?
- Are dimension keys unique, and do fact keys match the intended dimension rows?
- Do relationships point from dimensions to facts in the expected one-to-many pattern?
- Do slicers and combined filters produce results that agree with known data?
- Are alternate date roles handled deliberately rather than by unintended active paths?
- Do measures preserve the meaning of facts stored at a higher grain?
- Are technical columns hidden where appropriate, while useful labels and hierarchies remain discoverable?
- Could bidirectional relationships or complex paths create ambiguous filtering or extra query cost?
When a basic star schema is not enough
DirectQuery, composite models, row-level security, data reduction, and performance constraints can affect modeling choices. They have dedicated guidance and should be considered when they are central to the report’s source, security, or scale requirements, rather than treated as afterthoughts. Start with Microsoft’s Power BI guidance documentation to find the relevant topic. For broader dimensional-modeling foundations beyond Power BI, Microsoft also lists The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, third edition, as further reading in its star-schema guidance.
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.
