Locked lesson.

## About this lesson

In order to use fields from multiple tables on one PivotTable, we need to declare relationships between the tables. This lesson will explain what that means to us and the options we have in this space.

## Exercise files

Download this lesson’s related exercise files.

Key Concepts for Relating Data.docx66.7 KB Key Concepts for Relating Data - Solution.docx

66.7 KB

## Quick reference

### Key Concepts for Relating Data

An overview of 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 Pivot only truly supports One to Many relationships

#### Characteristics of One to Many relationships

- The column on the Many side of the relationship can contain many REPEATING values
- The column on the One side of the relationship MUST contain UNIQUE values
- When linking in relationship view, drag and drop will NEVER get this relationship backwards

#### Creating a One to One relationship

One to One relationships are not supported, but you can create something similar in Power Pivot

- Build it as a Many to One relationship.
- The relationship column on one side MUST contain UNIQUE values
- The relationship column on the other side CAN contain UNIQUE values
- When linking in relationship view, drag and drop might get this relationship backwards
- In Excel 2016, arrows flow toward the Fact table
- In Excel 2013, arrows flow toward Dimension table

### Hints & tips

- You do not need to flatten your data to link tables with Power Pivot as you do with Excel
- Hide the Many side of the relationship from the model to prevent future filtering errors

Lesson notes are only available for subscribers.