Power BI data modeling turns source tables into a semantic model that report visuals can filter, group, and summarize. A star schema is a strong starting point: dimensions describe the people, products, dates, or places used to analyze data, while fact tables record the events or values being analyzed. The key to getting it right is defining each fact table’s grain and making filter paths predictable.
What a Power BI data model does
A Power BI semantic model gives report authors an analytical view of a subject. When someone builds a visual, Power BI generates a query that filters, groups, and summarizes model data. A model’s tables, relationships, and measures determine how those operations behave. Microsoft recommends applying star-schema principles to produce a model with dimension and fact tables.
As an Amazon Associate I earn from qualifying purchases.
A star schema is a useful default, not a rule that settles every design choice. The right design depends on the questions the report must answer, the detail available in the source data, and constraints such as data volume or storage mode. Microsoft describes optimal model design as part science and part art. Microsoft’s star-schema guidance explains the pattern in detail.
Facts, dimensions, and grain
Dimensions provide context
A dimension describes an entity used to filter or group analysis: for example, a product, customer, location, or date. It normally has a key that uniquely identifies each row, plus descriptive columns such as product name or customer segment. A product dimension lets a report group sales by product category; a date dimension can support analysis by month, quarter, or year.
#1 Best Overall
Facts record events or values
A fact table records observations such as sales orders, inventory balances, exchange rates, or temperatures. It typically contains keys that connect each row to relevant dimensions and numeric values that can be summarized. A sales fact, for instance, might record quantities and amounts alongside product, customer, and date keys.
State the grain before building relationships
The grain is what one row in a fact table represents. It is determined by the combination of keys and other identifying values in that row. A sales fact might have one row per order line; a target table might have one row per product per month. In the latter case, even if the table stores a date value for the first day of each month, its grain is month by product—not day by product.
Keep the grain consistent within a fact table. If rows represent different levels of detail, measures and totals can become difficult to interpret. Also, do not assume two fact tables share a grain simply because both contain dates, products, or amounts.
Rank #2
How relationships control filtering
In the common one-to-many relationship, a dimension’s unique row is on the “one” side and the matching fact rows are on the “many” side. A report filter on a dimension—such as one product category—can then filter the related fact rows. In turn, a visual can group or summarize those facts in the selected context.
Relationships move filters through paths in the model; they do not validate or repair the underlying source data. A relationship is not a substitute for checking that dimension keys are unique and that fact-table keys match the expected dimension rows. Microsoft explains relationship behavior and filter propagation in its Power BI relationship guidance.
Use bidirectional filtering only when the model needs it and the resulting behavior is understood. Microsoft warns that it can hurt performance or create ambiguous filter paths. If more than one route can carry a filter between tables, it may be unclear which results a visual should show.
A practical sequence for building a model
- Start with report questions. Identify the business process the report will analyze and the decisions or comparisons it needs to support. List the dimensions users need for filtering and grouping, and state the grain of every fact table.
- Shape source data into facts and dimensions. If the source is a denormalized export, use Power Query to split and prepare it. For large data volumes or advanced warehouse patterns such as slowly changing dimensions, consider preparing the data through a data warehouse and ETL process before it reaches the semantic model.
- Create relationships from dimensions to facts. Use a unique dimension key and, in the usual design, a one-to-many relationship to the relevant fact table. If a dimension has no single unique key, a surrogate key may be needed; Power Query can add an index column to create one.
- Make the model understandable to report authors. Hide technical key columns from report view when they are needed only for relationships. Use clear names, useful hierarchies, and explicit measures where they improve navigation or make business definitions consistent.
- Validate the results. Test whether filters reach the intended facts and compare important measure results with known data. Check key uniqueness and matches separately: relationships propagate filters, but they do not enforce source-data integrity.
For a worked example of building a report from a dimensional model, see Microsoft’s Power BI dimensional-model tutorial.
When many-to-many relationships need care
Associations between dimensions
Two dimensions can have many-to-many associations—for example, a salesperson may cover several regions, and each region may have several salespeople. Microsoft advises against making a direct many-to-many relationship between dimension tables the default design. A bridge table can represent the associations; it is often a factless fact table, meaning it records links rather than numeric events.
Relationships between fact tables
Directly connecting two fact tables with many-to-many cardinality is generally not recommended. A more flexible pattern is to add shared dimensions—such as Date, Product, or Customer—and relate each dimension to each fact with one-to-many relationships. This gives report authors clearer ways to filter and group both facts and reduces the risk that data-integrity problems are obscured.
Facts recorded at different levels of detail
Suppose sales are recorded by day and product, while targets are recorded by year and category. The target is a higher-grain fact: it does not contain a distinct value for every day or product. A report that breaks the target down by a lower-level dimension may repeat or misrepresent it. Design measure logic to control how the higher-grain value is summarized rather than assuming the relationship alone makes every breakdown meaningful. Microsoft covers these cases in its many-to-many relationship guidance.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When one date table serves several date roles
A fact table may contain order date, due date, and ship date. Each date column represents a different analytical role, but only one relationship between a given Date table and that fact table can be active at a time. The active relationship filters by default; an inactive relationship is used only when a DAX expression activates it.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →For example, a model can use order date as the default date role and define a measure that uses USERELATIONSHIP to calculate by due date. This preserves one shared date dimension while making the alternate role explicit in the measure. See Microsoft’s guidance on active and inactive relationships.
Best Value
Measures and summarization
An explicit measure is a DAX formula that returns a single value in the context of a query—for example, total sales for the product and month selected in a visual. Measures can encode business definitions and provide consistent, governed summarization. A visual can also aggregate a numeric column directly through an implicit measure; not every numeric column needs a separately authored measure.
Use explicit measures when a definition should be reused or when the calculation needs deliberate logic, such as handling an alternate date relationship or controlling how a higher-grain fact is summarized. Keep simple columns available for direct summarization when that behavior is appropriate.
Choosing a design that fits the report
- Model flexibility: Can readers filter and group by the dimensions they need?
- Grain compatibility: Do related facts represent the same level of detail, or is a higher-grain fact involved?
- Filter behavior: Are relationships active or inactive, and does filter propagation follow a clear path?
- Integrity and performance: Are keys unique and matched as expected? Could relationship choices create ambiguous paths or extra query cost?
- Author usability: Are names understandable, technical keys hidden when appropriate, hierarchies useful, and measures consistent?
- Source and storage constraints: Can Power Query transformations fold to the source, or do data volume and complexity call for warehouse ETL or specialized DirectQuery or composite-model design?
DirectQuery, composite models, row-level security, data reduction, and performance tuning have separate guidance because they can affect modeling choices. Microsoft’s Power BI guidance documentation links to those specialized topics.
Recommended Free Tools
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.
