Ravindra BagaleCourses & study guides

11. Data Modelling

11.4 Relationships

By Last updated:

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

A common mistake is ignoring the (Blank) row that appears in slicers and visuals. It means some keys in the fact table have no match in the dimension (for example a new Store ID missing from DarkStore). Fix the dimension or the key cleaning, rather than filtering (Blank) out and hiding the problem. Don't worry – after doing it two or three times, it becomes a habit.