How to build a hierarchy to support drill mode in Microsoft Power BI

Users want to see your dashboard visuals, but they also want to see the underlying details. For instance, a visualization of sales by country displays good information. However, your users will also want to see sales for specific cities within a country and even customers within a city. Relationships make this possible, and hierarchies keep levels consistent when drilling.

In this tutorial, I’ll show you how to create a hierarchy that supports drill mode in Power BI. Once you have that hierarchy, you can quickly build visualizations that support drilling. At one time this was tedious work, but now it’s only a few clicks. You’ll learn what a hierarchy is, why you might need one and how to build one when you do.

I’m using Microsoft Power BI Desktop on a Windows 10 64-bit system. You can also use Power BI service, though you’ll need a Pro or Premium license. You can download the Microsoft Power BI demo file for this tutorial.

SEE: Microsoft Power Platform: What you need to know about it (free PDF) (TechRepublic)

How to determine if you need a hierarchy in Power BI

If users want to see details, you will both benefit from a hierarchy. A hierarchy is a quick way for you to satisfy drilling requirements and the user will always see the drill levels in the same order.

A hierarchy is a set of fields structured by levels. The hierarchy has a parent level and from there users can drill down through the lower levels, which contain details about the parent. The hierarchy requires at least two levels, a parent and a set of details, but a hierarchy can contain many levels.

SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)

You could build individual visualizations for each level and use filters to synchronize updates, but a hierarchy is quicker and easier. In short, a hierarchy allows users to explore data in a consistent manner. Creating the visualization manually will also enable drill mode, but once you have a hierarchy, all visualizations based on that hierarchy will be consistent and user-friendly.

Figure A shows two visualizations based on the same tables in the Adventure Works DW 2020 (v1) Power BI Desktop sample model. This model contains a hierarchy in the Sales Territory table. I based the visualization on the left on the hierarchy and added the Sales Amount field from the Sales table. I built the visualization on the right manually. Both support drill mode.

Figure A

Initially, the two visualizations appear to be the same, but looks are deceiving. Note the order of the fields in the hierarchy in the X-axis bucket: group, country and region. The Sales Amount field is in the Y-axis bucket. The drill buttons are below the visualization, and the black circle indicates that drill mode is on.

Figure B shows what happens when you click the double-arrow button, which drills down to the next level. They’re not the same, which might be a bit of a surprise to your end users if they’re used to seeing details in a different order.

Figure B

The visualization to the right is in country, group, region order. That means the parent levels aren’t the same. No matter how the end user drills, the experience won’t be the same as the one built on the hierarchy. In fact, the one to the right is a bit misleading because it appears to support the country level as the parent level. We can clearly see by using the hierarchy that the parent level is the group level, at least in the hierarchy.

The user should have the same drilling experience from one visualization to the next. That’s why you’ll want to create a hierarchy for drilling. Besides, it makes your work a bit easier.

How to add a hierarchy in Power BI

The demonstration file for this Power BI tutorial already has a hierarchy in the Sales Territory table. That hierarchy is in group, country, region order, which you saw earlier. Let’s add one that’s in country, group, region order. Most likely, you wouldn’t add this second hierarchy, but it’s a great way to compare the differences.

To add a hierarchy, click Model in the navigation pane to the left. Then, browse around until you find the Sales Territory table. The icon to the left of Sales Territories identifies the group of fields as a hierarchy.

To add a new hierarchy with country as the parent level to the Sales Territory table, right-click the Country field, the parent field, and choose Create Hierarchy from the resulting menu (Figure C).

Figure C

In the General pane to the right, enter a new name for the hierarchy, Sales Territories Country Parent, at the top of the pane.

From the Select A Column To Add dropdown, choose Group. Power BI adds the parent field, Country, for you.

From the same dropdown, choose Region (Figure D).

Figure D

Click the Apply Level Changes link below the Region field.

When you return to the Report window, you will find the new hierarchy in the Fields pane, as shown in Figure E. You now have two hierarchies on which to build visualizations. A table can have more than one hierarchy, but you’d probably not build two that simply reorder a couple of fields.

Figure E

Users probably won’t know you’re using a hierarchy, so the visualizations consistently show drill levels in the same order across all visualizations. However, they might notice if you don’t and not in a good way.