Introduction to Power Pivot - Microsoft Business Intelligence Tools for Excel Analysts - page 52

3. Select the appropriate table and field names from the drop-down lists to redefine the
relationship.

4. Click OK when you're done. Click the Close button in the Manage Relationships dialog box to
get back to the Power Pivot model.

In Figure 3-9, you see that the lower-left field is called Related Lookup Table. In this
drop-down list, you must select the table that contains unique non-duplicated rows. The
column you select in the Related Lookup Column must contain unique items.

Note

Creating a PivotTable from
Power Pivot Data
After you define the relationships in your Power Pivot Data Model, it's essentially ready for action. In
terms of Power Pivot, “action” means analysis with a PivotTable. Creating a PivotTable from a Power
Pivot Data Model is relatively straightforward:

1. Open the Power Pivot window. On the Home tab, click the PivotTable button.

2. Specify whether you want the PivotTable placed on a new worksheet or an existing sheet.

Limitations of Power Pivot-driven PivotTables

It's important to note that Power Pivot-driven PivotTables come with some limitations that you
don't encounter with standard PivotTables:

The Group feature is disabled for Power Pivot-driven PivotTables. You can't roll dates into
months, quarters, years, and so on. You can work around this using your own calculated col-
umns described later in this chapter.

●

In a standard PivotTable, you can double-click a cell to drill down to the rows that make up the
figure in that cell. In Power Pivot-driven PivotTables, however, you only get the first 1,000 rows.

●

Power Pivot-driven PivotTables won't allow you to create the traditional Calculated Fields and
Calculated Items found in standard Excel PivotTables.

●

Workbooks that use the Power Pivot Data Model cannot be refreshed or configured if opened
in a version of Excel earlier than Excel 2013.

●

You cannot use custom lists to automatically sort the data in your Power Pivot-driven PivotTables.

●

The Product and Count Numbers summary calculations are not available with Power Pivot-
driven PivotTables.

●