Key Concepts for Relating Data

Key Concepts for Relating Data

Key concepts you should know before relating data

When to use

When you are getting ready to link two or more tables together in the data model

Instructions

Power BI only supports three types of relationships: one-to-many, one-to-one, and many-to-many

One-to-Many relationships

• The relationship column on the One side of the relationship MUST contain UNIQUE values
• The relationship column on the Many side of the relationship MAY contain REPEATING values
• When linking in relationship view, drag and drop will NEVER get this relationship backwards
• Ensure you are using a single-direction filter (the arrow points to the many side only)

One-to-One relationships

• The relationship column in BOTH tables MUST contain UNIQUE values
• This relationship may need to be modified to get it correct
• This relationship type MUST use a bi-directions filter (the arrows point in both directions)
• These relationships can often be built using a one-to-many relationship

Many-to-Many relationships

• The relationship column may contain duplicate values on either side of the relationship
• This relationship type MUST use a bi-directions filter (the arrows point in both directions)
• These relationships can often lead to confusing outputs and should be avoided wherever possible

Hints & tips

• Bi-directional filters (double-headed arrows) can cause issues.  Avoid them unless they are required for a specific reason (most of the time they are not).
• Hide the many side of the relationship from the model to prevent future filtering errors