What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
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 stays predictable when each fact table has one stated grain, each dimension table has one row per key, and relationships pass filters from dimension to fact in a single direction unless a specific report need requires otherwise. A relationship does not merge tables. It tells Power BI how a filter applied in one table should reach another, which is why it differs from a join and why confusing the two is a frequent source of wrong totals.
“Joints” in the title is almost certainly a typo for “joins,” the term for combining tables. Power BI calls the model feature that links tables a relationship, so this guide covers both and keeps them apart. The Microsoft Learn guidance cited here was checked in October 2026. Dialog labels can change between Power BI Desktop releases, so confirm names against the version you have installed.
Start with the grain of each fact table
Before you open Model view, write one sentence for each fact table that says what a single row represents. “One row per sales order line” and “one row per warehouse per day” are usable grains. “Sales data” is not. The grain decides which columns belong in the table, which dimensions can attach to it, and what a sum of its rows actually means.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteMicrosoft’s guidance on star schemas recommends this shape for Power BI semantic models: dimension tables supply the attributes you filter and group by, and fact tables supply the numeric values you summarize. The table below sets out the two roles.
#1 Best Overall
| Role | What it holds | Row rule | Illustrative example |
|---|---|---|---|
| Dimension | Descriptive attributes used to filter and group, such as product name, category or calendar month | One row per key, so the key column must be unique | Product table with one row per ProductKey |
| Fact | Measurable events or values to summarize, such as quantity, amount or cost | Many rows per dimension key, all sharing one declared grain | Sales table with one row per order line |
In that illustrative pattern, many order lines share the same ProductKey, so the Product dimension filters the Sales fact through a one-to-many relationship. The example shows how the roles fit together; it is not a benchmark or a tested model.
Avoid copying dimension attributes into a fact table for convenience, such as repeating a product’s category on every order line. That creates a second copy that must stay in step with the dimension, and it blurs which table owns the attribute.
Relationships and joins are different operations
A model relationship defines a filter path between two tables. Microsoft’s article on model relationships states: “A model relationship propagates filters applied on the column of one model table to a different model table.” (Microsoft Learn, Model relationships in Power BI Desktop)
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →A join combines the columns of two tables into one result. In Power BI you usually meet joins as Merge Queries in Power Query Editor, which offers join kinds such as Left Outer and Inner, or as a JOIN inside a source database query. The table below compares the two.
| Question | Relationship | Join (Merge in Power Query or a source query) |
|---|---|---|
| What it does | Lets a filter on one table reach another during analysis | Adds columns from one table to another and can add or remove rows |
| Tables afterward | Remain separate in the model | Become one combined table |
| Effect on row count | Creates no rows in either table | Multiplies rows when the matching side has duplicate keys; an inner join drops unmatched rows |
| Where it is defined | Model view or Manage relationships | Power Query Editor or the source query |
Use a join when you need one wide table that carries columns from two sources, such as enriching a lookup list before it loads. Use a relationship when the tables sit at different grains and reports should filter across them. The distinction matters for grain: merging a fact table with a table at a different grain changes what one row means, while a relationship leaves each table’s grain untouched.
Cardinality: one-to-many is the usual shape
Cardinality describes how many rows on each side of a relationship can carry the same key value. Power BI Desktop offers four settings.
Rank #2
| Cardinality | Meaning | Typical use | Check before you accept it |
|---|---|---|---|
| One to many (1:*) | Key unique on the “one” side, repeated on the “many” side | Dimension to fact, the standard star pattern | Dimension key is unique and not blank |
| Many to one (*:1) | The same relationship read from the fact side; the default setting | Fact to dimension, the same pattern viewed from the other table | Same as above |
| One to one (1:1) | Key unique on both sides | Uncommon in a star schema | Both tables describe the same entity at the same grain, not a split of one table |
| Many to many (*:*) | Duplicate keys allowed on both sides | Limited cases; see the many-to-many section | The business relationship is genuinely many-to-many |
Cardinality is a statement about your data, not a setting that makes the data correct. Automatic detection is a starting guess. Before you accept a cardinality, check the following.
- Uniqueness on the “one” side. Compare the row count with the distinct count of the key column in the dimension. Any gap means duplicate keys.
- Blank keys on either side. A blank dimension key cannot match a fact row as you expect.
- Matching data types. A text key such as “00123” will not match a whole number 123. Fix the type in Power Query, not in the relationship.
- Unmatched keys. Fact keys with no dimension row will not filter anything. Count them before you trust a total.
Desktop checks uniqueness when it can, but do not rely on that check alone. If a one-to-many relationship will not save, treat the error as a signal to find the duplicates. Switching to many-to-many to get past it hides the problem.
Setting up a relationship in Power BI Desktop
Create the relationship only after the grain and keys are validated. Then follow these steps.
- Select the Model view icon in the left navigation bar.
- Drag the key column from the dimension table onto the matching key column in the fact table. Power BI opens the Create relationship dialog.
- Confirm the table and column names in both dropdowns. Read the table order carefully, because it determines what “one” and “many” refer to.
- Set Cardinality to match the uniqueness you validated.
- Set Cross filter direction to Single for the first relationship between the two tables. Use Both only for a documented scenario, as described below.
- Leave Make this relationship active checked for the path most reports need. Clear it for a secondary path, such as a second date role.
- Select OK, then check the result with a Card visual that shows a simple sum of the fact column. Compare it with the source total.
To review or edit relationships later, select Home > Manage relationships. The Autodetect button there proposes relationships from matching column names and types. Treat its output as a draft and verify each cardinality and direction against the data.
Filter direction and when bidirectional filtering is justified
In a single-direction relationship, a filter moves from the one side to the many side. On a Product-to-Sales link, a Product slicer narrows the Sales rows, and a measure that sums Sales reflects only the selected products. The Sales table does not filter the Product table back.
Recommended Free Tools
Both (bidirectional) allows filters to propagate in both directions. Microsoft cautions that bidirectional relationships can negatively affect performance and can introduce ambiguous filter paths, and it recommends using them only where the scenario calls for them. (Microsoft Learn, Create and Manage Relationships in Power BI Desktop)
Bidirectional filtering is often added to make a visual show the expected result. That can make one symptom disappear while the grain or key problem stays in place, and it creates a second route through the model. Fix the grain and keys first. If a bidirectional filter is still needed, limit it to the one scenario it serves and test that report on its own.
Active and inactive relationships
Between two tables, Power BI uses one active relationship as the default path. Other relationships between the same tables can exist but remain inactive. A measure uses an inactive relationship only when it asks for it with USERELATIONSHIP.
A common case is a fact table with two dates. Sales might have an OrderDateKey relationship that is active and a ShipDateKey relationship that is inactive, and both relate to the Date table. A measure can then ask for shipments:
Sales by Ship Date =
CALCULATE(
SUM(Sales[SalesAmount]),
USERELATIONSHIP(Sales[ShipDateKey], 'Date'[DateKey])
)A visual that uses the plain SalesAmount measure still follows the active OrderDateKey path. Name measures so readers can see which path they use. USERELATIONSHIP applies to one calculation; it does not change the model’s default path, and it does not repair a missing date or a duplicate key.
CROSSFILTER changes or disables relationship propagation for a single calculation, and TREATAS can apply values from one column to a column that is not related to it in advanced scenarios. Neither replaces a sound base model. Reach for them after the relationships are correct.
Many-to-many: what the setting does and what it does not
The many-to-many setting permits duplicate key values on both sides of a relationship. It does not repair bad keys, and it does not tell you which grain a report should use.
Rank #4
Microsoft generally advises against relating two fact tables directly this way. Visuals then have limited filtering and grouping flexibility, and integrity issues can cause rows to be omitted. The recommended alternative is a star schema in which dimension tables relate to each fact table through one-to-many relationships. (Microsoft Learn, Many-to-many relationship guidance)
Consider Sales and warehouse Inventory snapshots. Both contain many rows per product, so relating them directly looks like a fix. The better design is a shared Product dimension with one-to-many relationships to each fact, and shared Date and Warehouse dimensions in the same way.
Some business relationships really are many-to-many, such as customers who hold several accounts, where each account also has several customers. In that case, use a bridge table:
- Create a bridge table with one row per valid pair of CustomerKey and AccountKey. The grain of the bridge is the pair, so a duplicated pair counts twice.
- Relate Customer to the bridge one-to-many on CustomerKey, and relate Account to the bridge one-to-many on AccountKey.
- Decide the filter direction for each relationship from the reports’ real questions: which selections must reach which tables.
- Test with known cases before trusting totals, such as a customer with two accounts and an account shared by two customers.
The bridge design must be validated against your actual data and reporting requirements. It is a pattern for a genuine business relationship, not a shortcut for relating two fact tables.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Composite models and limited relationships
A composite model combines storage modes or data sources in one model, for example Import tables alongside DirectQuery tables, or tables from different sources. Microsoft’s documentation on composite models, found on Microsoft Learn under the composite models article for Power BI Desktop, describes relationships that cross sources as behaving differently from relationships within one source. They can be limited, can carry performance implications, and can constrain how DAX retrieves rows from the “one” side.
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 →In limited relationship evaluation, table expansion does not occur, and the joins are resolved at query time with inner-join semantics. Unmatched rows can therefore be missing from results rather than shown in a blank group. In a standard relationship, fact rows whose key has no dimension match usually appear under a (Blank) member in visuals that use the dimension column. If that member is absent where you expect it, suspect limited evaluation or a filter that removed the rows.
Do not assume every relationship in a composite model behaves the same way. Check each relationship’s details in Manage relationships, then test the unmatched-key case in a copy of the source or a test environment. Confirm that the rows you expect appear in the visual.
Troubleshooting sequence
The steps below follow Microsoft’s relationship troubleshooting guidance, reorganized for scanning. (Microsoft Learn, Relationship troubleshooting guidance) Work through them in order; each step narrows the next.
- Confirm the data is loaded. Open each table in Table view or Data view, depending on your Desktop version. Expected result: the tables contain rows and the counts match the source query preview.
- Check the grain and key uniqueness. For each fact table, confirm the row grain you wrote down. For each dimension, the distinct count of the key equals the row count. Expected result: one row per dimension key.
- Verify the relationship columns. Confirm compatible data types, no unintended blanks, and no stray spaces or case differences in text keys. Expected result: key values and types match on both sides.
- Inspect the relationship. In Home > Manage relationships, check cardinality, active status and cross filter direction. Expected result: the active path is the one your measure should use.
- Trace filter direction and paths. Look for Both directions, loops and multiple routes between the same tables, especially after enabling Both. Expected result: one route from each slicer to each table.
- Check limited and cross-source behavior. If rows are missing across sources or through a limited relationship, count the unmatched keys and consider the inner-join behavior described above.
- Compare a simple visual with the source. Build a Card with a plain sum of the fact column and compare it with the source total. Expected result: any difference is explained by a specific filter or unmatched key. Only then add DAX workarounds.
Symptom-based branches
Totals are higher than expected
The likely causes are duplicate keys on the “one” side, a second path between the same tables, or a many-to-many relationship that allows duplicates. Start at steps 2 and 5. Fix the duplicates at the source or in Power Query so the dimension holds one row per key, or correct the fact grain. Do not switch to many-to-many to stop a total from changing; that keeps the duplicates and removes the check that exposed them.
Free tools Windows power users keep installed
One-click scans. No signup required.
Rows are missing
The likely causes are unmatched keys, mismatched data types, a filter applied to the dimension, or inner-join behavior in a limited relationship. Start at steps 3 and 6. Count the fact keys with no dimension match, and confirm that the key types agree. If the missing rows belong to a cross-source relationship, check that relationship’s evaluation behavior before changing any DAX.
Filters are ambiguous or pick the wrong date
The likely causes are multiple paths between tables, Both direction on a relationship that did not need it, or a measure that uses an inactive relationship without saying so. Start at steps 4 and 5. Search the measure definitions for USERELATIONSHIP, CROSSFILTER and TREATAS, and confirm that each one is intentional. If a fix requires one of these functions to produce the right answer, revisit the base model before keeping it.
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.
