11.4 Relationships

By Ravindra Bagale Last updated: 25 September 2026

Power BI often creates relationships automatically when column names and types match (autodetect). Always check them in Model view or Modeling › Manage relationships.

To create a relationship manually: in Model view, drag the key column from one table onto the matching column of another table, or use Manage relationships › New.

Cardinality

Cardinality	Meaning	Example
*One-to-many (1:)**	One row in the dimension relates to many rows in the fact	`Product` 1 → * `Orders`
*Many-to-one (:1)**	Same as above, seen from the other direction	`Orders` * → 1 `Product`
One-to-one (1:1)	Unique on both sides	`Employee` ↔ `Employee Details` (usually better merged into one table)
*Many-to-many (:)*	Neither column has unique values	`Orders` ↔ `City Targets` (targets per city per month, a different grain) – use carefully

In the diagram, 1 and * labels appear at each end of the relationship line.

Cross-filter direction

Single (default for 1:): filters flow from the one side (dimension) to the many* side (fact). Selecting "Snacks" in Product[Category] filters Orders.
Both (bi-directional): filters flow in both directions. Sometimes needed (e.g. to filter a slicer of customers to only those who bought), but it can cause ambiguity (संदिग्धता – एकापेक्षा जास्त अर्थ किंवा मार्ग), unexpected results and slower performance.

Avoid 'Both' unless you really need it

Prefer single direction. If you need bi-directional behaviour for one calculation, use DAX CROSSFILTER(…, Both) inside CALCULATE for just that measure instead of changing the relationship for the entire model. For slicers, a visual-level filter such as [Total Sales] is not blank often solves the need.

Active and inactive relationships

Mhanje asa: between two tables, only one relationship can be active at a time. In our sample, Orders has two dates – Order Date and Delivered Date – both related to Date[Date]. They usually match, pan an order placed at 11:55 pm may be delivered after midnight, on the next date:

Date[Date] → Orders[Order Date] is active (solid line). All visuals use it by default.
Date[Date] → Orders[Delivered Date] is inactive (dotted line). It is used only when a measure activates it with USERELATIONSHIP:

Sales by Delivered Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(Orders[Delivered Date], 'Date'[Date])
)

Alternative approach: create a second date table (a role-playing dimension, e.g. Delivered Date) – but this increases model size.

Relationship properties dialog

Double-click a relationship line to open it. You can set Cardinality (दोन तक्त्यांतील ओळी किती-किती जुळतात याचे प्रमाण), Cross filter direction, Make this relationship active, Apply security filter in both directions (for RLS with bi-directional filters) and Assume referential integrity (DirectQuery only – allows inner joins, faster queries).

Samjla ka? Relationship chi direction dimension kadun fact kade. Nasel tar diagram punha ekda bagha.

Ravindra Bagale's Tip

Ek common chuk mhanje ignoring the (Blank) row that appears in slicers and visuals. It means some keys in the fact table have no match in the dimension (udaharan mhanje a new Store ID missing from DarkStore). Fix the dimension or the key cleaning, rather than filtering (Blank) out and hiding the problem. Ghabru naka, don-teen vela kela ki savay hote.