Forum Discussion
Relationships/Cardinality
Hi cingram11 ,
Thank you for reaching out to the Microsoft Fabric Community. Also responses provided by AlexisOlson and speedramps offer valuable insights, particularly around the concept of column cardinality and how it affects relationship design.
To add further clarity, in the context of Power BI and data modeling, a relationship defines how two tables are logically connected so that data from one table can filter or aggregate data in another. These relationships are governed by cardinality, which refers to how many rows in one table relate to rows in another.
Common types include one-to-one (1:1), one-to-many (1:M), and many-to-many (M:M). For instance, a one-to-many relationship is often created between a dimension table (e.g., Customers) and a fact table (e.g., Sales), where each customer can have many sales records, but each sale belongs to only one customer.
Column cardinality, as explained earlier, describes the number of distinct values in a column and plays a crucial role in determining the most efficient and correct relationship structure. For a detail information in relationship cardinality in Power BI, please refer to the document below: Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.
Thank you.
Relationships and Cardinality are 2 different things
Cardinality is the frequency that somethings occurs
For example
The Shop column has low Cardinality because there is just one value (Asda)
The Transactiion ID has high Cardinality because each is unique
The Name has medium Cardinality because there are just 3 (Fred, Mark and James)
This is good learning video about relationships. I recommend you watch it a few times.
https://www.youtube.com/watch?v=OOs-VWf20E8
A one to many relationship (as the name suggests) must have a unique value on the parent table.
For example, the Transaction ID can support a 1:M relataionship because it is unique.
But Name cant support a 1:M relationship in the above table, because Fred has multiple rows.
Please click thumbs up and the [accept solution]
Cardinality of a column is its distinct count. In speedramps example,
Cardinality(Name) = 3
Cardinality(Item) = 2
Cardinality(Shop) = 1
Cardinality(Transation ID ) = 5
Cardinality of relationships is slightly different. Here's a good resource on it:
Many-to-One or Many-to-Many? The Cardinality of Power BI Relationship Demystified - RADACAD
