- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Using Automatic Relationship Detection
The easiest way to define a relationship is to add a field from the other worksheet into your pivot table. The pivot table results will initially be wrong, and PowerPivot will alert you that a relationship needs to be defined.
Click the Create button. PowerPivot will examine both tables to see if it can figure out a column that both worksheets have in common. In this case, even though Store and StoreID are different headings, PowerPivot noticed that both columns had the same Store Number values and detected the relationship.
This is cool when it works. If it doesn't work, you can resort to explicitly defining the relationship.