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.
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
Video: Introduction to Power Pivot relationships
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 I define a relationship, I 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 will not create the relationship.
- Select the column in that table that will serve as the source or foreign key. For example, SportID.
- Select the Related Column that will serve as the target or primary key. Sometimes Excel will pre-populate this field if there is an exact column name match. For example, SportID.
- Select the Related table. For example, Sports.
- Use the dropdown to select the source Table. For example, Disciplines.
When you 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 they are created, 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 impact of data from one previously-unrelated table on your visualizations.
Top of Page
Power View and Power Pivot videos
Power View: Explore, visualize, and present your data
Tutorial: PivotTable data analysis using a Data Model in Excel 2013
Power Pivot reporting properties for Power View
Power Pivot: Powerful data analysis and data modeling in Excel
Top of Page