Understand model relationships

  • 6 minutes

A model relationship propagates filters applied on the column of one model table to a different model table. Filters will propagate so long as there's a relationship path to follow, which can involve propagation to multiple tables.

Relationship paths are deterministic, meaning that filters are always propagated in the same way and without random variation. Relationships can, however, be disabled, or have filter context modified by model calculations that use particular DAX functions. Use DAX relationship functions are described in Unit 2.

Important

Model relationships don’t enforce data integrity. Unit 4, which describes relationship evaluation, explains how model relationships behave when there are data integrity issues with your data.

Here's how relationships propagate filters with an animated example.

Animated diagram shows how relationship filter propagation as described in the following two paragraphs.

In this example, the model consists of four tables: Category, Product, Year, and Sales. The Category table relates to the Product table, and the Product table relates to the Sales table. The Year table also relates to the Sales table. All relationships are one-to-many (the details of which are described in the next unit).

A query, possibly generated by a Power BI card visual, requests the total sales quantity for sales orders made for a single category, Cat-A, and for a single year, CY2018. It's why you can see filters applied on the Category and Year tables. The filter on the Category table propagates to the Product table to isolate two products that are assigned to the category Cat-A. Then the Product table filters propagate to the Sales table to isolate just two sales rows for these products. These two sales rows represent the sales of products assigned to category Cat-A. Their combined quantity is 14 units. At the same time, the Year table filter propagates to further filter the Sales table, resulting in just the one sales row that is for products assigned to category Cat-A and that was ordered in year CY2018. The quantity value returned by the query is 11 units. Note that when multiple filters are applied to a table (like the Sales table in this example), it's always an AND operation, requiring that all conditions must be true.

Apply star schema design principles

We recommend you apply star schema design principles to produce a model comprising dimension and fact tables. It’s common to set up Power BI to enforce rules that filter dimension tables, allowing model relationships to efficiently propagate those filters to fact tables.

The following image is the model diagram of the Adventure Works sales analysis data model. It shows a star schema design comprising a single fact table named Sales. The other four tables are dimension tables that support the analysis of sales measures by date, state, region, and product. Notice the model relationships connecting all tables. These relationships propagate filters (directly or indirectly) to the Sales table.

Screenshot of a Power B I Desktop model diagram comprising the tables and relationships as described in the previous paragraph.

Use disconnected tables

It's unusual that a model table isn't related to another model table. Such a table in a valid model design is described as a disconnected table. A disconnected table isn't intended to propagate filters to other model tables. Instead, it accepts "user input" (perhaps with a slicer visual), allowing model calculations to use the input value in a meaningful way. For example, consider a disconnected table that’s loaded with a range of currency exchange rate values. As long as a filter is applied to filter by a single rate value, a measure expression can use that value to convert sales values.

The Power BI Desktop what-if parameter is a feature that creates a disconnected table. For more information, see Create and use a What if parameter to visualize variables in Power BI Desktop.