My blog post from earlier this year about bidirectional relationships and many-to-many in Power BI sparked a lot of interest. What I didn’t realise at the time is that there’s another new feature (albeit rather less exciting) concerning relationships: you can now create one-to-one relationships between tables.
For example, consider the following two tables:
Both contain a column called Fruit containing the same, distinct set of values. If you load both these tables into Power BI Desktop, create a relationship between them and make sure the Cardinality is set to 1:1 and Cross Filter Direction to Both, like so:
…then not only do you get bi-directional cross-filtering (ie if I select something from Fruit1 it will filter the Fruit2 table, and if I select something from Fruit2 it will filter Fruit1) but the Related() and RelatedTable() functions can be used in a DAX calculated column on either table to look up values in the other. With one-to-many relationships, you can only use Related() in a calculated column on the ‘many’ side of the relationship and RelatedTable() on the ‘one’ side of the relationship.
Thanks to Marius Dumitru for pointing this out to me!
One thought on “One-To-One Relationships In Power BI”