Lesson 4 of 11 · 9 min · Modelling data
Relationships, cardinality and filter direction
What you will learn
- What a relationship does in a Power BI model
- Cardinality: one-to-many, one-to-one and many-to-many
- Cross-filter direction and why single direction is the default
- Active and inactive relationships
- How to spot and fix relationship problems
In simple words
In the star schema, tables are separate. A relationship is the string that ties two tables together on a shared column, such as CustomerKey. Once tied, clicking "Hyderabad" in the Customer table automatically filters the Sales table to Hyderabad's sales. Without the string, a chart that mixes customer city with sales amount shows the same total for every city.
Words to know
- Relationship: a link between two tables on a matching column.
- Cardinality: how many rows on one side can match rows on the other side.
- Filter direction: which way a selection travels across a relationship.
- Active relationship: the one relationship between two tables that filters by default.
What a relationship does
A relationship lets a filter on one table propagate to another. With Customer related to Sales on CustomerKey, a visual showing Customer[City] and SUM(Sales[Amount]) splits the sales by city: each city's row of the chart filters the Sales table to just that city's rows.
Create relationships in Model view by dragging a column from one table to the matching column in the other, or through Manage relationships. Power BI also auto-detects some on load, so always check them. The matching columns must have the same data type, and the "one side" column must contain unique, non-blank values.
Cardinality
| Type | Meaning | Example | Use |
|---|---|---|---|
| One-to-many (1:*) | one row in table A matches many in table B | one customer, many orders | the standard star-schema relationship |
| Many-to-one (*:1) | the same, from the other table's view | Sales to Customer | identical to the above; just the direction you think of it |
| One-to-one (1:1) | each row matches at most one row | an employee and their profile | rare; usually better to merge the two tables |
| Many-to-many (:) | many rows match many rows on both sides | students and courses, or budget (by category) and sales (by product) | needs care; use a bridge table when possible |
For a normal star schema, every relationship from a dimension to a fact is one-to-many, with the dimension on the "one" side (the side with the unique key).
Many-to-many relationships create ambiguity: because neither column is unique, the engine may count rows more than once or not match them as you expect. Safer designs:
- Use a bridge (junction) table with unique keys on both sides, connected to each table with a one-to-many relationship: for example
StudentCoursebetweenStudentandCourse. - Build the dimension with a unique key by removing duplicates in Power Query, then relate it one-to-many.
- Use a many-to-many relationship only when you understand the behaviour and have tested the totals.
Filter direction
Each relationship has a cross-filter direction:
- Single (the default and the best): filters flow from the "one" side (dimension) to the "many" side (fact). Filtering Customer filters Sales, but filtering Sales does not filter Customer.
- Both (bidirectional): filters flow both ways. It can make slicers show only valid values (for example only the products that actually sold), but it can cause ambiguous filter paths, slow performance and surprising numbers.
Guidance: keep single direction. If you need a slicer to show only related items, consider a measure-based filter or fix the model before turning on bidirectional filtering. When you must use it, prefer enabling it with a measure using CROSSFILTER or on one relationship that you have tested, never across the whole model by default.
Active and inactive relationships
Between two tables there can be only one active relationship at a time, drawn as a solid line; others are inactive (dashed). A common case: Sales has OrderDate, ShipDate and DeliveryDate, all linking to the Date table. Make the one you use most (OrderDate) active. For a measure using the other, activate the inactive relationship inside the measure with USERELATIONSHIP:
Sales by Ship Date =
CALCULATE ( SUM ( Sales[Amount] ), USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )
Another solution is role-playing dimensions: load the Date table more than once as Order Date and Ship Date, each with its own active relationship.
Referential integrity and blanks
If a sales row has a CustomerKey that does not exist in the Customer table (an "orphan"), Power BI groups it under a (Blank) row in visuals. Totals still include it, so a total might not equal the sum of the visible categories. Find orphans in Power Query (a Left Anti merge) and fix them or add an "Unknown" row to the dimension.
Diagnosing relationship problems
| Symptom | Likely cause |
|---|---|
| Every row of a visual shows the same total | no relationship between the table you group by and the measure's table |
| A (Blank) category appears | orphan keys, or blank keys in the fact |
| Totals are too high | a many-to-many or duplicate keys in the "one" side; a join in Power Query that multiplied rows |
| A slicer does not filter a visual | filter direction does not reach that table, or the tables are not connected |
| "Cannot determine relationship" or ambiguity error | more than one filter path between two tables |
| Time intelligence returns nothing | the Date table is not marked or not related |
Check with a simple table visual showing the key columns and a count: clean data and relationships should give the right numbers there before you build a fancy chart. A quick audit in DAX: Customers without sales = COUNTROWS ( FILTER ( Customer, ISEMPTY ( RELATEDTABLE ( Sales ) ) ) ).
Good practice
- One active path between any two tables.
- Dimensions on the one side, facts on the many side, with single direction filtering.
- Hide the key columns in the fact table, and avoid relationships on text columns that may change spelling.
- Name relationships' tables and keys consistently, for example
CustomerKeyeverywhere. - Keep the model diagram tidy: facts in the middle, dimensions around them.
Common mistakes
- Leaving auto-detected relationships unchecked.
- Turning on bidirectional filtering everywhere.
- Using many-to-many without a bridge table and trusting the totals.
- Relating on columns with different types or non-unique values on the "one" side.
- Having several active paths and not knowing which one filters.
Try it yourself
- In Model view, relate Customer, Product and Date to Sales and confirm each is one-to-many, single direction.
- Add
ShipDateto Sales, relate it to Date (it becomes inactive), and write a measure usingUSERELATIONSHIP. - Create a table visual of
Customer[City]andSUM(Sales[Amount]); remove the relationship and observe the result, then restore it. - Introduce an orphan key and find the (Blank) row.
Check yourself
Which side of a one-to-many relationship holds the unique values?
The "one" side, normally the dimension table.
What is the safest cross-filter direction and why?
Single (dimension to fact), because it keeps filter paths unambiguous and performance high.
How do you use an inactive relationship in a measure?
Wrap the calculation in CALCULATE with USERELATIONSHIP(column1, column2).
Why does a visual sometimes show the same total for every category?
The category's table has no relationship (or no filter path) to the table the measure sums, so the filter does not reach the measure.
Interview questions
What is cardinality and which types does Power BI support?
The number of matching rows between two tables on the relationship columns: one-to-many, many-to-one, one-to-one and many-to-many.
How would you handle a many-to-many relationship?
Introduce a bridge table of unique keys connected one-to-many to both tables, or de-duplicate one side into a proper dimension; use native many-to-many only when its behaviour is understood and totals are tested.
Why would a model have an inactive relationship?
Because two tables can have only one active relationship; secondary links, such as ship date alongside order date, stay inactive and are activated in specific measures with USERELATIONSHIP.
Summary
- Relationships propagate filters from dimensions to facts; build them in Model view with matching, unique keys.
- Prefer one-to-many with single-direction filtering; avoid many-to-many and bidirectional unless needed and tested.
- One active relationship between two tables; use
USERELATIONSHIPfor the others. - Debug with simple table visuals, orphan checks and a clear model diagram.
Going deeper
- Model relationships in Power BI Desktop: cardinality, filter direction and active relationships.
- Relationship troubleshooting guidance: when to use bidirectional filtering.
- USERELATIONSHIP function (DAX): activating inactive relationships.
See how much of this you can explain
A free mock interview asks you new questions on Power BI and dashboards for Data Analyst and scores every answer, with what to fix.