Power BI relationships: how tables connect and filters flow
Power BI relationships are the links between tables that let one filter reach another. When a reader picks a product category, a relationship carries that choice to the sales table, and the total changes. Get the links right, and measures stay short and correct. Get them wrong, and numbers double, vanish or ignore a slicer. This guide explains the settings and the usual errors.
- Settings decoded
- Filter flow explained
- Six errors to avoid
Last updated
Power BI relationships: what are they and why do they matter?
A relationship joins a column in one table to a matching column in another. A product table and a sales table, for example, both hold a product key. Once linked, the two behave as one connected set.
This matters because reports filter by descriptions yet total by transactions. Readers choose a category or a month. The sales rows themselves carry only keys and amounts. So the relationship is what lets the choice reach the numbers.
Desktop can detect some links automatically when data loads. However, automatic detection guesses from column names, and it sometimes guesses wrong. Therefore an author should always review the model view before trusting a total.
What does each setting on a relationship mean?
The table lists the terms you meet in the relationship dialog, with a plain meaning for each.
| Term | Meaning | Typical use |
|---|---|---|
| One-to-many | One row on one side matches many on the other | Product table to sales table |
| One-to-one | Each row matches a single row | Two tables that describe the same item |
| Many-to-many | Both sides can repeat a value | Special cases, handled with care |
| Single direction | Filters flow one way only | The default for most models |
| Both directions | Filters flow each way | Occasional needs, after testing |
| Active | The link filters by default | The main date or key link |
| Inactive | The link waits until a measure calls it | A second date, such as ship date |
How do filters travel through Power BI relationships?
In a one-to-many link with single direction, filters travel from the one side to the many side. A choice on the product table narrows the sales table. The reverse does not happen, so a filter on sales leaves the product list whole.
That one-way flow is a strength. It keeps results predictable, because each filter has a single route. A star schema uses it throughout: dimension tables sit around a fact table, and every filter points inward.
Both-direction filtering lets a filter move back up as well. It solves a few real problems, such as a slicer that should show only items with sales. Still, Microsoft's guidance advises caution, since it can slow queries and create ambiguous paths.
Why may only one active path link two tables?
Because the engine must know which route a filter takes. When two tables connect through more than one link, such as order date and ship date, only one can be active. Otherwise a date filter would have two possible meanings.
The other link stays inactive, and it still has value. A measure can switch it on for one calculation with the USERELATIONSHIP function. So shipped revenue by month uses the ship date link, while the default measures use order date.
Some authors prefer a second copy of the date table instead. Then each copy has its own active link, and readers can slice by either date. Both methods work, and the better one depends on how people read the report.
Power BI relationships mistakes that skew totals
Check a model against this list first whenever a number looks wrong.
- Linking on a column that holds duplicates
- Joining two fact tables directly
- Turning on both directions by habit
- Leaving blank or mismatched keys unexamined
- Trusting automatic detection without review
- Skipping a dedicated date table
Power BI relationships questions from report authors
What is an inactive relationship for?
It keeps a second link available without confusing the default filter path. The common case is a fact table with several dates. One date link stays active, while the others wait. Then a measure activates one of them for a specific calculation, and the remaining measures continue as before.
When is a many-to-many relationship acceptable?
When the data truly has that shape and you have tested the totals. Budgets at category level against sales at product level are one example. However, many-to-many links often signal a missing dimension table. So first ask whether a proper table of unique values would give a cleaner model.
Should I ever use both-direction filtering?
Sometimes, for a specific need. It can help a slicer show only relevant values, for instance. Still, apply it to one relationship at a time and check performance afterward. In many cases a measure or a visual-level filter reaches the same goal with less risk to the model.
Does Power BI create relationships automatically?
Desktop can detect them when you load data, based on column names and values. That saves time on simple models. However, it may link the wrong columns or miss a link entirely. Therefore many authors review every detected link, and some switch the option off for larger models.
Can someone repair a model with tangled links?
Yes. Power BI Agency does this work often. One of our consultants maps the current model, redesigns it toward a star schema and retests each key measure. We work online inside your tenant and show progress weekly. We are a division of Sataware Technologies, independent of Microsoft, and the first assessment is free.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.