Hierarchies

To create a hierarchy, select the column in the Fields list that will represent the top level of the hierarchy and use the ellipsis or right-click the menu to select the New hierarchy option, as shown in the following screenshot:

Creating a hierarchy

In this example, the Product Category Group column is the top level of the hierarchy and Product Category will be its child or lower level. Likewise, the Product Subcategory column will be a child of Product Category and the Product Name column will be the lowest level of the hierarchy under Product Subcategory. To add columns to the hierarchy, click the ellipsis next to the given column or use the right-click context menu to choose the Add to hierarchy option. Alternatively, the child columns can be dragged and dropped onto the name of the hierarchy by holding down the left mouse button when selecting the column. The levels of the columns can also be adjusted from within the hierarchy by dragging and dropping column names.

Dimension tables often contain hierarchical data, such as dates (year, quarter, month, week, day) and geographies (country, state/province, city, zip code). As shown in the example of the date dimension in the previous chapter (see the Date dimension view section), natural date hierarchies in which each column value has only one parent (for example, 2017-Sep) are strongly recommended. Unnatural date hierarchies can be confusing in reports as it isn't clear which parent value (2015, 2016, 2017?) a given child value, such as September, belongs to.

Once the hierarchy is created, a single click of the hierarchy name in the fields list adds all the columns and their respective levels to the report visualization. In the following screenshot, all four columns of the Product Hierarchy are added to the Axis of a column chart to support drilling and interactive filter behavior in Power BI:

Hierarchy in Report Visual

Certain columns or levels of the hierarchy can optionally be removed from the specific visual. For example, if the report developer only wishes to include Product Category and Product Subcategory in a particular visual, the other two columns can be removed from the Axis field well via the delete (X) icons.

The DAX language includes a set of parent and child functions, such as PATH() and PATHITEM(), that can be used to create hierarchy columns when a dimension table contains a Parent Key column. Common examples of this include an organizational structure with multiple levels of management or a chart of financial accounts. Creating these columns via DAX functions is one of the few examples when DAX-calculated columns may be preferable to other alternatives. A detailed example of parent and child functions was included in Chapter 3 of the Microsoft Power BI Cookbook by Packt Publishing
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.188.119.81