Relationships in Power View

When creating Power View reports on data that comes from different sources, you need to create relationships between the data if you want to build reports and create visualizations based on all the data. By creating relationships, you join data together in a cohesive unit called a data model.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

There are several ways to create relationships. This topic explains how to create them in Power View. For more information on relationships in general:

Create a relationship between two tables

Create a Data Model in Excel

Relationships and Data Models

When you create a relationship, you are building a data model by joining together data from previously-unrelated data sources. For more information on data models, see Create a Data Model in Excel.

Top of Page

Create a relationship in Power View

A relationship is a connection between two tables of data, based on one column in each table that contain similar or identical data. For example, the Olympic Host City and CountryRegion tables can be related to each other because they both contain a column that stores a 3-digit geography code.

From the Power View tab, > Relationships. This opens the Manage Relationships window. From here you can create new relationships, edit, deactivate, and delete existing relationships
To create a new relationship, click New. The Create Relationships window opens.
When you define a relationship, you connect a source column in the first table, sometimes called a foreign key column, to a target column in the second table, sometimes called a primary key column.

Note: The values in the related target column must be unique or Excel doesn't create the relationship.
Select the source Table. For example, Disciplines.
Select the Column in that table to serve as the source or foreign key. For example, SportID.
Select the Related table. For example, Sports.
Select the Related Column to serve as the target or primary key.

Sometimes Excel pre-populates this field if there is an exact column name match. For example, SportID.

Click OK.

Power View creates the relationship and adds it to the underlying data model. You can now create visualizations based on data in both tables – in this example, in both the Disciplines and Sports tables.

Relationships, regardless of where you create them, allow you to do things such as:

Combine data from different tables and different data sources into a single visualization
Filter, highlight, aggregate, and slice one or more visualizations with fields from previously unrelated tables
Explore the impact of data from one previously-unrelated table on your visualizations.

Top of Page

Microsoft Office Tutorials

Monday, March 6, 2017