iTechGuides 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 semantic model decides how a slicer click becomes a filtered total. To make that behavior predictable, build the model as a star schema: fact tables record events or measurements at one explicit grain, dimension tables supply the descriptive columns used to filter and group, and each dimension connects to its facts through a one-to-many relationship whose “one” side holds a unique key. Filters flow from the dimension to the fact by default, and two fact tables that must be reported together should share dimensions rather than relate to each other directly.
Relationships in Power BI are filter paths, not SQL joins performed when the model is built. Most surprises in a model, such as blank rows, totals that double count, and refresh failures, trace back to a mismatch between table roles, grain, cardinality, and filter direction.
How fact and dimension tables map to report behavior
Microsoft’s star-schema guidance draws the two roles in short statements: “Dimension tables enable filtering and grouping.” and “Fact tables enable summarization.” (Microsoft Learn: Understand star schema and the importance for Power BI). Every visual asks the semantic model to filter, group, and summarize. Dimensions answer the question “by what?”, and facts supply the values being added up.
Recommended Free Tools
| Aspect | Fact table | Dimension table |
|---|---|---|
| What it records | Events or measurements, such as sales order lines or budget entries | Entities or descriptive attributes, such as dates, products, customers, or regions |
| Meaning of one row | One row at the declared grain, for example one sales order line | One member of the entity, for example one product |
| Typical columns | Numeric measures and a foreign key to each related dimension | Descriptive text, categories, hierarchy levels, and one unique key |
| Role in visuals | Supplies the values that are summed, averaged, or counted | Supplies axis labels, slicer items, and filter values |
| Side of a one-to-many relationship | Usually the many side | Usually the one side |
Define the grain before drawing any relationship
The grain of a fact table is what one row represents. Microsoft’s guidance says fact tables should always load at a consistent grain. Write the grain as one sentence per fact table before creating any relationship. For example:
#1 Best Overall
- Sales: one row per sales order line, with quantity and net amount.
- Budget: one row per product, region, and calendar month.
These two facts should not be merged into one table. A header-level charge such as freight, if stored on every order line, is repeated once per line, and summing the column counts it several times. Keep such values in a fact at the header grain, or allocate them to lines deliberately in Power Query before loading. Mixing grains without a deliberate step is the most common source of double counting.
From exported tables to model tables
Source exports are usually denormalized: category names repeated on every product row, or an entire order flattened into one wide file. Power Query can reshape such data into several normalized tables, and Microsoft’s guidance notes that a snowflake dimension can sometimes be denormalized into a single model table when that is appropriate. Normalization is therefore a transformation choice, not a requirement to copy every source-system table boundary into the report model.
A practical rule follows from that. Keep an attribute inside its dimension when reports only ever filter or group by it, such as category and subcategory on a product table. Split it into its own table when several dimensions share it, it carries attributes of its own, or it needs a relationship of its own.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
How relationships define filter paths
A relationship connects a column in one table to a column in another. When a user selects a value on one side, Power BI propagates that filter across the relationship. The “one” side must contain unique values, and the “many” side may repeat them. Microsoft documents four cardinality options: one-to-many, many-to-one, one-to-one, and many-to-many (Model relationships in Power BI Desktop). Power BI Desktop can infer cardinality when you create a relationship, but the inference is a suggestion. Confirm that the data actually matches it.
Rank #2
| Cardinality | Uniqueness required | Typical use | Notes |
|---|---|---|---|
| Many to one, or one to many read from the opposite table | The lookup table’s key | Fact to dimension | The default star-schema pattern. It is the same link described from either end. |
| One to one | Both columns | Two tables that each hold one row per entity | Filters both ways, per Microsoft’s relationship documentation. |
| Many to many | Neither column must be unique | Keys that repeat on both sides, or two fact tables | A limited relationship. Covered in the many-to-many section. |
Validate the unique side before refresh
If refresh attempts to load duplicate values on the one side of a relationship, the refresh fails. The fix belongs in the data, not in the relationship. Check the key in three steps:
- In Power BI Desktop, open Home > Manage relationships, select the relationship, and note the two columns it uses. Confirm that the dimension column is the one you expect to be unique.
- Add a temporary measure to the model to count duplicate keys:
Product key duplicates = COUNTROWS(Product) - DISTINCTCOUNT(Product[ProductKey]). A result of 0 means the key is unique. - To find fact rows with no dimension match, open Home > Combine > Merge queries in Power Query, merge the fact query with the dimension query, and choose the Left Anti join kind. The rows returned are the unmatched keys.
Remove duplicates in Power Query only after you have decided which row is correct. Removing them without that decision can keep the wrong version of a product or customer.
Filter direction: single by default
Cross-filter direction sets which way a selection travels across a relationship. For a one-to-many relationship, propagation runs from the one side to the many side by default. Setting the direction to Both allows propagation from either side (Model relationships in Power BI Desktop). One-to-one relationships filter both ways, and many-to-many direction can be set from one table, the other, or both.
| Direction | What propagates | Where it fits | Cost and risk |
|---|---|---|---|
| Single (default for one-to-many) | From the dimension to the fact | Star-schema reports, which is the usual baseline | Facts cannot filter dimensions, which is normally the intended behavior |
| Both | From either side | A specific, tested layout in which a lookup must filter a table that otherwise cannot receive the filter | Can affect performance and create ambiguous filter paths when several lookup tables share a route |
Microsoft’s relationship-management guidance warns against Both where multiple lookup tables and shared paths create ambiguity (Create and Manage Relationships in Power BI Desktop).
Do relationships work like SQL joins in Power BI?
Not in the sense most readers expect. SQL-style joins happen in two places: in the source database, and in Power Query, where a merge produces a new table with combined columns and rows. A model relationship does not produce combined rows. It records how filters travel, and Power BI applies that path at query time.
Microsoft classifies relationships as regular or limited, based on cardinality and source group. Many-to-many relationships and cross-source relationships are limited. In import models, joins for limited relationships are resolved at query time, and table expansion does not occur. The table below shows the difference.
| Mechanism | Creates combined rows? | When it is resolved | Typical use |
|---|---|---|---|
| Power Query merge, with join kinds such as Left Outer, Inner, or Left Anti | Yes, a new physical table | During refresh | Bringing a lookup column into a table before it loads |
| Regular model relationship | No; filters propagate across the relationship | At query time, through filter context | Standard fact-to-dimension links |
| Limited model relationship | No; the join is resolved at query time, and table expansion does not occur in import models | At query time | Many-to-many and cross-source designs that a specific requirement calls for |
Many-to-many: two different problems
Many-to-many appears in two distinct situations, and each calls for a different design.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →A mapping where keys repeat on both sides
Consider products that belong to several promotions, where each promotion covers many products. Both key columns repeat, so a direct link between the two is many-to-many. The standard answer is a bridge table with one row per product-promotion pair, related one-to-many to each dimension. Microsoft’s many-to-many guidance describes this pattern (Many-to-many relationship guidance – Power BI). Verify the bridge’s grain, the filter direction on each link, and that every pair key matches a row in both dimensions.
Rank #4
Two fact tables that must be analyzed together
Relating two fact tables directly with many-to-many cardinality is generally not recommended. Microsoft’s guidance says that in this design the report can only filter and group through the shared key, and data integrity issues can cause rows to be omitted (Many-to-many relationship guidance – Power BI). The official alternative is to add shared dimension tables and relate each fact to them one-to-many. That lets you filter by shared attributes and summarize either fact (Many-to-many relationships in Power BI Desktop).
For example, Sales and Budget can both relate to a Product dimension and a Region dimension. A slicer on Region then filters both facts, and each measure keeps the grain of its own table. The two facts can be compared by shared attributes without any fact-to-fact link.
A direct many-to-many relationship is still a supported option for specific requirements, so it is not wrong by definition. Before choosing it, compare it with the shared-dimension pattern on your own data, and check filter direction, grain, integrity, and the results of the visuals you actually build.
DirectQuery and composite models change the rules
In DirectQuery, Power BI sends queries to the underlying source rather than holding the data in the model. Microsoft’s DirectQuery guidance cautions against bidirectional filtering unless it is needed, in part because the generated queries may perform poorly (DirectQuery model guidance in Power BI Desktop).
Assume Referential Integrity
The Assume Referential Integrity setting on a relationship can allow source queries to use inner joins rather than outer joins. That is efficient when every fact key has a matching dimension row. When some fact rows have no match, an inner join excludes them from results. Enable the setting only when the data meets that assumption, and confirm it with the unmatched-key check described earlier.
Composite models and cross-source relationships
Composite models combine storage modes or sources in one model (Use composite models in Power BI Desktop). A relationship that crosses sources is a limited relationship, and it can carry performance effects of its own. Microsoft also notes limitations when DAX retrieves values across a cross-source relationship.
Microsoft’s composite model guidance (Composite model guidance in Power BI Desktop) adds these practical rules:
- Use low-cardinality relationship columns. The guidance recommends fewer than 50,000 unique values, especially when combining tabular models and for non-text columns. This is Microsoft’s recommendation, not a platform maximum.
- Use care with long text keys.
- Watch for ambiguous paths where the same dimension can be reached by more than one route.
Troubleshooting blank groupings and unexpected totals
A blank row in a visual is often an unmatched key rather than a filter-direction problem. Microsoft’s troubleshooting guidance identifies unmatched many-side values as one possible cause of blank groupings (Relationship troubleshooting guidance). Work through the causes in this order:
Quick Recap
- Place the fact table’s foreign key column in a table visual. A blank group that carries values means fact rows with no matching dimension row.
- Run the Left Anti merge from the validation steps to list the unmatched keys, then correct them in the source or in Power Query.
- Confirm that the relationship’s cardinality and direction still match the data, using the sections above.
- Only after those checks pass, test a change to cross-filter direction on one representative visual, and then check whether other visuals in the report shift.
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.
