Free tools Windows power users keep installed
One-click scans. No signup required.
For most Power BI reports, start with a star schema: define what one row in each fact table represents, then connect descriptive dimension tables to those facts with one-to-many relationships. Use single-direction filtering as the straightforward default, and add bridges, alternate date paths or many-to-many relationships only when the analysis requires them.
What is a star schema in Power BI?
A star schema organizes a model around fact tables and dimension tables. A fact table records events or measurements at a consistent grain; a dimension table holds descriptive attributes people use to filter and group those facts. In the usual arrangement, dimensions sit on the one side of one-to-many relationships and facts sit on the many side.
As an Amazon Associate I earn from qualifying purchases.
For example, a sales fact might have one row per order line, with product, customer, date and quantity or revenue values. Product, customer and date dimensions provide readable categories for grouping or filtering those rows. The sales table’s grain matters: a total has a clear meaning only when you know what each row represents.
Recommended Free Tools
These roles describe how a table is used in the model, not a special table-type setting. An operational source schema may be normalized for data entry or storage; it does not necessarily make the clearest reporting model. Microsoft Learn’s guidance on star schemas recommends treating grain, fact and dimension responsibilities as deliberate design decisions. It presents star schema as a strong pattern, not a rule that every model must follow.
#1 Best Overall
Choose the grain before connecting tables
Write down what one row means for every fact table, such as “one row per invoice line” or “one row per monthly account balance.” Check that the source actually conforms to that definition. If one table mixes transaction-level rows with summary rows, measures and totals may not behave as intended even when relationships are configured correctly.
How do I create relationships in Power BI?
Identify the matching key columns, verify that the intended dimension key is unique, and connect that dimension to the corresponding repeated foreign key in the fact table. In Power BI Desktop, create or inspect the relationship in the model diagram or relationship-management controls, then verify its cardinality, active status and cross-filter direction. Microsoft Learn’s “Create and Manage Relationships in Power BI Desktop” and “Model relationships in Power BI Desktop” explain the relationship settings.
- Identify the table roles. Decide which table contains descriptive lookup values and which contains the events or measurements to analyze.
- Check the key columns. The dimension-side key should have one row per value. Confirm that the related columns use compatible data types and represent the same kind of identifier.
- Connect the columns. Create the relationship between the dimension key and the fact’s foreign key, then confirm Power BI has assigned the intended cardinality.
- Set a clear filter path. Start with the dimension filtering the fact in one direction unless the analysis needs a different path.
- Validate with a simple visual. Put a dimension field and a fact measure in a table or matrix. Check that the groups and totals match the underlying rows you expect.
A relationship is not merely a line on a diagram. Cardinality describes whether key values are unique or repeated, while cross-filter direction determines how filters propagate through the model. A wrong key, incompatible data type or unintended filter path can produce confusing results even though a relationship exists.
What does relationship cardinality mean?
Cardinality tells Power BI how values are distributed across the two related columns. It should reflect the data, not be chosen just to make a relationship save.
Rank #2
| Cardinality | What it means | Typical use |
|---|---|---|
| One-to-many | Each key value occurs once on the one side and may repeat on the many side. | A unique product or customer key related to repeated foreign keys in a fact table. |
| One-to-one | Each related key value occurs at most once in each table. | Two tables with a genuinely one-to-one key relationship; confirm that splitting the data across tables is needed. |
| Many-to-many | Key values can repeat on both sides. | Use only when the relationship truly represents many-to-many associations and the filter behavior is understood. |
If Power BI cannot use the intended one-to-many pattern, investigate whether the supposed dimension key contains duplicates, blanks or inconsistent values. Changing the relationship to many-to-many may conceal a data-quality issue rather than solve it.
How should filters flow through the model?
Single-direction filtering from a dimension to its related fact is a legible default: a product selection filters sales rows, for example. Bidirectional filtering allows filters to travel in both directions, but additional paths can make it harder to predict why a visual includes or excludes rows. Use it only for a specific requirement, and test representative report interactions after changing it.
Microsoft’s DirectQuery model guidance warns that bidirectional relationships can impair query performance. In DirectQuery, relationship settings and referential-integrity assumptions can also affect the queries Power BI sends to the source. Do not apply bidirectional filtering as a general fix for a visual that looks wrong; first trace the existing path and inspect the keys.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Inactive relationships for alternate paths
A model can contain an inactive relationship for an alternate interpretation of a key, such as shipment date alongside order date. The inactive path does not serve as the default active filter path. A measure can activate the alternate relationship when that calculation needs it. This can keep one shared date dimension while supporting distinct calculations, but it requires measure authors to understand which path each measure uses.
Disconnected tables for selected inputs
A disconnected table intentionally has no relationship that propagates its filters into the model. It can supply a user-selected input, such as a what-if parameter, that a calculation reads separately. Do not connect a table merely because it appears in a slicer; a disconnected design can be intentional when selection is meant to affect a measure rather than filter rows directly.
Should I use an explicit date table or Auto date/time?
Use an explicit date dimension when facts need a shared calendar, custom calendar attributes or DAX time-intelligence analysis. Microsoft Learn’s date-table guidance requires a date or date-time column containing unique values for a date table. A shared dimension can filter multiple fact tables through separate relationships.
Auto date/time can be convenient for simple calendar exploration, but it does not provide one shared date dimension that filters several tables. If the report compares multiple facts over the same calendar, an explicit date table makes that common filter structure visible.
| Choice | Useful when | Trade-off |
|---|---|---|
| Explicit date table | Multiple facts share a calendar, custom calendar attributes are needed, or DAX time-intelligence functions are used. | Requires maintaining a deliberate date dimension and its relationships. |
| Auto date/time | Simple calendar exploration is sufficient and a shared date dimension is not required. | Does not create one shared calendar dimension for filtering multiple tables. |
Handling order, shipment and delivery dates
When a fact has several date roles, choose between an inactive relationship and separate role-playing date dimensions. An inactive relationship with measures is compact and useful when alternate date logic is mainly measure-driven. Separate date dimensions, such as Order Date and Ship Date, allow simultaneous filtering by those roles and can be clearer for report authors, at the cost of duplicating a relatively small dimension.
Rank #4
If a fact is stored at a higher grain than a day, such as one row per month, align its period value to a representative date, for example the first day of that month. Daily date filters do not automatically define the intended logic for a period-level fact; make explicit how calendar selections should include or exclude those rows.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When should I use a many-to-many relationship?
Use many-to-many modeling when the underlying association genuinely allows multiple members on both sides, and choose the design based on the entities and filter paths involved. For two dimensions with overlapping associations, a bridge table is often the clearer representation: it contains one row per association and connects to the dimensions with one-to-many relationships.
For example, if a customer can belong to several account groups and each group can contain several customers, a bridge table can record the customer-group pairs. Hide technical identifiers or the bridge table from ordinary report authors when those fields do not help them build reports. Some bridge designs require a bidirectional path so filters can travel through the bridge; treat that as a deliberate exception and validate the resulting behavior.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Do not default to a direct many-to-many fact relationship
When two fact tables need to be compared, Microsoft generally recommends shared dimensions rather than a direct many-to-many link between the facts. As Microsoft Learn puts it in “Many-to-many relationship guidance – Power BI”: “Generally, we don’t recommend you relate two fact tables directly by using many-to-many cardinality.”
Best Value
For example, if sales and returns both relate to products and dates, shared product and date dimensions can filter each fact through its own one-to-many relationships. A direct fact-to-fact link can restrict how visuals group and filter results and may obscure integrity problems.
| Design choice | What to weigh |
|---|---|
| Bridge table for dimension associations | Whether the bridge records real entity pairs, how filters propagate through it, and whether its technical fields should be exposed to report authors. |
| Shared dimensions for multiple facts | Whether the facts have compatible grain and can be grouped by the same dimensions, and whether measures remain meaningful when shown together. |
| Direct many-to-many fact link | Whether a direct relationship is truly necessary; check grouping behavior, integrity visibility and query complexity before choosing it. |
Why are my Power BI totals wrong or blank?
Start by tracing the visual’s filter path and inspecting the underlying row groupings. A table or matrix can show which dimension values and fact rows contribute to a result more clearly than a chart alone. Microsoft’s relationship troubleshooting guidance recommends investigating relationship settings and data integrity when results are unexpected.
- Check uniqueness: verify that the key on the one side really has one row per value.
- Check key compatibility: confirm the related columns have compatible data types and use the same identifier format.
- Check the fact grain: determine whether the measure is being summed across transaction rows, summary rows or a mixture.
- Check unmatched values: look for fact foreign keys absent from the dimension, blank keys and other integrity problems that can appear as blank groups.
- Trace filter direction: follow the route from the slicer or dimension to the fact behind the visual, including any inactive or bidirectional paths.
- Inspect visible rows: temporarily use a table or matrix to expose the categories and values being returned before changing the model.
- Review DirectQuery behavior: if the model uses DirectQuery, assess bidirectional filtering and referential-integrity assumptions in the context of the source queries.
How to choose between common model designs
Use the smallest, clearest design that supports the required analysis. For each candidate, check whether its grain is consistent, totals remain meaningful, the necessary dimensions can filter each fact, and report authors can understand the paths.
Quick Recap
| Decision | Prefer this when | Consider the alternative when |
|---|---|---|
| One-way or bidirectional filtering | A dimension-to-fact path handles the required filters. | A specific bridge or other documented path requires filters to travel back through a relationship; test for ambiguity and DirectQuery cost. |
| Inactive relationship or role-playing dimensions | Alternate date logic can be handled in measures and one shared calendar is useful. | Report authors need to filter multiple date roles at once and the clearer active paths justify duplicated date dimensions. |
| Bridge or direct many-to-many dimension relationship | A bridge accurately records associations and clarifies entity membership. | A direct relationship is simpler and its filtering, integrity and authoring behavior are demonstrably clear. |
| Shared dimensions or direct many-to-many fact link | Shared dimensions can filter both facts and offer flexible visual grouping. | The model has a specific requirement that shared dimensions cannot meet; verify grain alignment, totals, integrity and query behavior. |
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.
