In the last blog post we explored data schemas - the foundation of Power BI. Without them, reliable analysis and visualization fall apart.
Equally important are the relationships that come into play as soon as we work with more than one data source or table in Power BI - moving beyond a flat schema toward a relational model.
To stick with our building analogy - if data schemas are the foundation, then the relationships form the supporting structure. Both are crucial for stability and usability.
Cardinality
In Power BI, relationships between tables are defined through shared keys. This means that each table must contain a matching column - for example, an element ID.
In Power BI, cardinality defines the relationship between tables by specifying how many rows in one table correspond to rows in another.
Put into BIM language, cardinality describes whether a component is associated with exactly one type, severaltypes, or whether the same type can occur across many different components - and therefore must be handled accordingly in Power BI.
In Power BI, relationships are shown in the Model view as lines connecting the tables. Selecting one of these connections reveals its details in the Properties window.
Relationships can be created either by dragging and dropping fields in the Model view or by using the Manage Relationships dialog box.
While Power BI typically creates the correct relationship by default, it is important to understand and review these settings to avoid unexpected results.
The most important property of a relationship in Power BI is cardinality. Power BI offers four possible cardinality types:
1 : n (One-to-Many) or n : 1 (Many-to-One)
This is the ideal situation: a row in table A can be linked to many rows in table B, while each row in table B belongs to exactly one row in table A (or vice versa).
Power BI loves this relationship - filters are clear, performant and comprehensible. A good example of this can be found in the Assets Dashboard, for which you can refer to this vdeo tutorial .
In this example, the data exchange and the asset information are linked via the element ID. At first glance, you might expect a 1:1 relationship, since a component typically corresponds to an asset - but there’s a good reason why that’s not the case.
As mentioned in the previous article, the Data Exchange Connector loads all data into a single table - both the actual element information and the viewer data required to display the model. You may notice many seemingly empty rows in the table view; these contain important viewer-related information and should not be deleted.
For this reason, a 1:1 relationship with the data exchange is usually not possible, as this type of relationship requires each row to have a unique value - and even an empty field counts as a value.
Btw, the Assets Dashboard can be found in the Data Exchange Sandbox ACC project - access to this project can be requested here.
1 : 1 (One-to-One)
This type of relationship is relatively rare in BIM data: each row in table A maps to exactly one row in table B. It usually appears when element-specific information is stored in a separate table rather than shared across multiple components.
When working with Data Exchange, however, this relationship does occur in the context of Federated Views - specifically when two loaded Data Exchanges are displayed together in the same viewer as an aggregated model. To enable this, the two Federated Viewer Mapping columns must first be connected. Power BI automatically recognizes this as a 1:1 relationship.
Click here for the official documentation for this scenario.
n : n (Many-to-Many)
In this case, several rows in table A can correspond to several rows in table B - without a clear assignment. This scenario should generally be avoided, as it often leads to ambiguous results and contradictory analyses. If it does occur, the data schema should be reviewed and optimized.
Cross-filter Direction
All relationships have a cross-filter direction, which is ideally set to Single.
A particularly popular feature in Power BI is bi-directional relationships, i.e. relationships where filters work in both directions. This is particularly tempting in the BIM context: you click on a room and immediately see all the associated components, click on a type and see all the rooms in which it occurs. It feels intuitive and „right“.
And that is also the big advantage: bi-directional cross-filter direction can be very helpful for initial analyses and smaller models.
The problem often only becomes apparent as complexity increases. Multiple bidirectional relationships can create unpredictable filter paths: results become difficult to explain, performance degrades, and it is no longer clear why a visualization shows certain values - or none at all. In larger BIM data models, this can introduce logical loops.
Bi-directional relationships are therefore not a mistake, but a tool - one that should be used deliberately and sparingly.
The general recommendation is to start with simple, single-direction relationships and switch to bidirectional ones only when necessary—while carefully monitoring the results.
Conclusion: Relationships are the supporting structure
A good Power BI data model is not so different from a good BIM model. It may not be spectacular, but it is logically structured, easy to understand, and designed to grow. Relationships are not a minor detail - they are the supporting structure of the entire system.
Dashboards are the facade. Relationships are the supporting structure.
If you’d like to learn more about working with Revit data in Power BI, be sure to sign up for our webinar next week. And if you’re reading this blog post later—don’t worry, I’ll add a link to the recording as soon as it’s available.
👉🏼 Click here to go to the Webinar registration - I look forward to seeing new and familiar faces and, as always, exciting discussions! 🙂
P.S. The recording from the last webinar, in which we talked about the new features in the Data Connector for Power BI 2.4.0 have reported, can be found here..