Overview of Model Relationships in Power BI Desktop
We shall discuss import data modelers working with Power BI Desktop considered as an important model design. It is considered essential to deliver intuitive, accurate, and optimal models. Primarily, model relationship paths are considered deterministic, indicating that filters are always propagated in the same manner and without random variation. Also, relationships can, be disabled, or may have filter context modified by model calculations that use particular DAX functions. It’s very crucial for you to understand that model relationships do not enforce data integrity.
Disconnected Tables
In general, it is rare that a model table is not related to another model table. Further, such a table in a valid model design can be described as a disconnected table.
- Firstly, a disconnected table is not meant to propagate filters to other model tables.
- Secondly, disconnected tables are used to accept “user input” (perhaps with a slicer visual), allowing model calculations to use the input value in a meaningful way.
Model Relationships Properties
As you know model relationship relates one column in a table to one column in a different table.
Cardinality
Every model relationship must be defined with a cardinality type. In general, the following represents the cardinality type options, representing the data characteristics of the “from” and “to” related columns. The “one” side means the column contains unique values; the “two” side means the column can contain duplicate values. The four options are –
- One-to-many (1:*)
- Many-to-one (*:1)
- One-to-one (1:1)
- Many-to-many (*:*)
Cross filter direction
Every model relationship has to be defined with a cross filter direction. Moreover, your selection determines the direction(s) that filters will propagate. The possible cross filter options are dependent on the cardinality type.
| Cardinality type | Cross filter options |
|---|---|
| One-to-many (or Many-to-one) | Single Both |
| One-to-one | Both |
| Many-to-many | Single (Table1 to Table2) Single (Table2 to Table1) Both |
Relationship Evaluation
From an evaluation perspective, Model relationships, are categorised as regular or limited. But, remember, it is not configurable relationship property. Further, it is essential to understand the evaluation type since there may be performance implications or consequences should data integrity be compromised.
- A model relationship is regular when the query engine can determine the “one” side of relationship. It has confirmation that the “one” side column contains unique values. All One-to-many intra source group relationships are regular relationships.
- A model relationship is limited when there’s no guaranteed “one” side. It can be performed in case the relationship uses a Many-to-many cardinality type or the relationship is cross source group.
For more visit – Model Relationships
