Hierarchies in PowerPivot
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
A hierarchy is a viewable list, a collection of columns that you create as child levels to place in any order in the hierarchy. Hierarchies can appear separate from other columns in a reporting client tool, making them easier for client users to select and navigate the common paths of data.
Tables can include dozens or even hundreds of columns with complex column names. Because of this, client users might have difficulties finding and including data in a report. The client user can add the entire hierarchy (consisting of multiple columns) to a report in only one click. Hierarchies can also provide a simple, intuitive view of data structure. For example, in a Date table, you can create a Calendar hierarchy. Calendar Year is used as the top-most parent level, with Month, Week, and Day included as child levels (Calendar Year->Month->Week->Day). This hierarchy shows a logical relationship from Calendar Year to Day.
Hierarchies can be included in perspectives. Perspectives define viewable subsets of a model that provide focused, business-specific, or application-specific viewpoints of the model. A perspective, for example, could provide users a hierarchy of only those data items necessary for their specific reporting requirements. For more information, see Perspectives in PowerPivot.
You can create, edit, and delete hierarchies from the Diagram View. For more information about Diagram View, see PowerPivot Window: Diagram View. You can also view hierarchies in the PowerPivot and Excel Field List. (If you are using SQL Server Data Tools (SSDT), click the Model menu, and then click Analyze in Excel.) This topic includes the following sections:
Create a Hierarchy
Edit a Hierarchy
Delete a Hierarchy
Hierarchies in the Field List
Create a Hierarchy
You can create a hierarchy by using the columns and table context menu or by using the Create Hierarchy button on the table header in Diagram View. When you create a hierarchy, a new parent level appears with the columns you selected as child levels. When you create a hierarchy, you create a new object in your model. You do not move the columns into a hierarchy; you create additional objects. A single column can be added to multiple hierarchies.
Note
You can create a hierarchy from a hidden column (a column that is hidden from client tools).
If you know what columns you want created as child levels in your hierarchy, the Create Hierarchy command in the context menu enables you to multi-select those columns and quickly create a hierarchy with multiple child levels.
To create a hierarchy from the context menu
While in Diagram View, select one or more columns in a table.
To open the context menu, right-click one of the selected columns. If you want to create a hierarchy from only one column, you can right-click the column without selecting it first.
Click Create Hierarchy. A parent hierarchy level is created at the bottom of the table, and the selected columns are copied under the hierarchy as child levels.
Type a name for your new hierarchy.
You can then drag more columns into your hierarchy’s parent level, which creates child levels from the columns and places the levels at the bottom of the hierarchy.
Or, if you want a column in a particular location of the hierarchy list, you can drag a column to create and place the child level where you want it to appear in the hierarchy.
When you use multi-select to create a hierarchy, the order of the child levels is initially, automatically organized based on the cardinality of the columns. The highest cardinality is listed first, where values are the most uncommon and unique, such as identification numbers and names, and the columns with the lowest cardinality is listed last, where values are more common, such as status, Boolean values, or common classifications. However, adding additional columns places child levels at the bottom of the list. You can drag the columns to change the order. For more ways to change the order of the child levels, see Edit a Hierarchy below.
Note
A hierarchy can be based on columns from within a single table only. If you multi-select another object type (such as a measure or KPI) along with one or more columns, or if you select columns from multiple tables, the Create Hierarchy command is not available in the context menu. To add a column from a different table, use the RELATED DAX function to add a calculated column that references the column from the related table. For more information, see RELATED Function. The function uses the following syntax:
=RELATED(TableName[ColumnName])
If you only have one column you want created as a child level in your hierarchy or if you are not sure which columns to add as child levels to your hierarchy, then you can create a hierarchy parent level by using the table header. Then you can drag columns into it as child levels, adding them to an exact location in the hierarchy.
To create a hierarchy from the button in the table header
Click the Create Hierarchy button in a table header. An empty hierarchy parent level appears at the bottom of the table.
To create columns as child levels under the hierarchy parent level, drag the columns onto the hierarchy.
Note
If use the Create Hierarchy feature (from the table header button or the table’s context menu), you will always create an empty hierarchy. Any selection of columns you have done will be ignored. If you selected one or more columns in the table, the columns are not added under the hierarchy. You must drag the columns into the hierarchy. If you want to select multiple columns to place in a hierarchy, multi-select the columns, and then use the context menu command instead.
Edit a Hierarchy
You can rename a hierarchy, rename a child level, change the order of the child levels, add additional columns as child levels, remove a child level from a hierarchy, show the source name of a child level (the column name), and hide a child level if it has the same name as the hierarchy parent level.
To change the name of a hierarchy or child level
Right-click the hierarchy parent level or a child level, and then click Rename.
Type a new name or edit the existing name.
Or, double-click the parent hierarchy, and then edit the name.
To change the order of a child level in a hierarchy
Drag a child level into a new position in the hierarchy.
Or, right-click a child level of the hierarchy, and then click Move Up to move the level up in the list, or click Move Down to move the level down in the list.
Note
You can add a column only once to a single hierarchy. After you add a column to a hierarchy, you cannot add it to the same hierarchy again. As a result, you will not be able to drag a column into a hierarchy, and the Add to Hierarchy context menu for the particular column will no longer reference the hierarchies to which the column has already been added. If there are no other hierarchies to which a column can be added, the Add to Hierarchy option does not appear in the menu.
To add another child level to a hierarchy
Drag a column onto the parent level of the hierarchy. The column is copied as a child level at the bottom of the hierarchy list.
Or, drag a column into a specific location in the hierarchy. The column is copied as a child level of the hierarchy.
Or, right-click a column or multiple selected columns, point to Add to Hierarchy in the context menu, and then click the specific hierarchy. A child level is created from the column and added to the bottom of the hierarchy list.
Note
You can add a hidden column (a column that is hidden from client tools) as a child level to the hierarchy. The child level is not hidden.
To remove a child level from a hierarchy
Right-click a child level, and then click Remove from Hierarchy.
Or, click a child level in the hierarchy, and then press Delete. If you want to get the child level back, you must add the column again.
Note
If you rename a hierarchy child level, it no longer shares the same name as the column that it is created from. By default, the source column name appears to the right of the child level. If you hide the source column name, use the Show Source Column Name command to see which column it was created from.
To hide or show a source name
Right-click a hierarchy child level, and then click Hide Source Column Name or Show Source Column Name to toggle between the two options.
When you click Show Source Column Name, the name of the column that it was copied from appears to the right of the child level.
Delete a Hierarchy
To delete a hierarchy and remove its child levels
Right-click the parent hierarchy level, and then click Delete.
Or, click the parent hierarchy level, and then press Delete. This also removes all the child levels.
Hierarchies in the Field List
The hierarchy appears as a folder in the Field List. You can open the folder to see the child levels. To edit the hierarchy levels, return to Diagram View.
See Also
Reference
Concepts
PowerPivot Window: Diagram View