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


A person loading Microsoft Power BI on a laptop. Image: PhotoGranary/Adobe Stock Users want to see your control panel visuals, but they likewise wish to see the underlying information. For instance, a visualization of sales by nation displays excellent information. Nevertheless, your users will also wish to see sales for particular cities within a nation and even consumers within a city. Relationships make this possible, and hierarchies keep levels consistent when drilling.

Must-read big data protection

In this tutorial, I’ll reveal you how to create a hierarchy that supports drill mode in Power BI. When you have that hierarchy, you can quickly construct visualizations that support drilling. At one time this bored work, now it’s just a few clicks. You’ll discover what a hierarchy is, why you might need one and how to build one when you do.

I’m utilizing Microsoft Power BI Desktop on a Windows 10 64-bit system. You can also utilize Power BI service, though you’ll need a Pro or Premium license. You can download the Microsoft Power BI demonstration filefor this tutorial.

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

How to figure out 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 method for you to satisfy drilling requirements and the user will constantly see the drill levels in the very 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 needs a minimum of two levels, a parent and a set of information, however a hierarchy can include numerous levels.

SEE: Hiring set: Microsoft Power BI designer (TechRepublic Premium)

You might develop specific visualizations for each level and use filters to synchronize updates, but a hierarchy is quicker and easier. Simply put, a hierarchy permits users to check out information in a consistent way. Developing the visualization manually will also allow drill mode, once you have a hierarchy, all visualizations based upon that hierarchy will be consistent and user-friendly.

Figure A reveals two visualizations based upon the very same tables in the Experience Works DW 2020 (v1) Power BI Desktop sample model. This model consists of a hierarchy in the Sales Area table. I based the visualization on the left on the hierarchy and included the Sales Amount field from the Sales table. I built the visualization on the best manually. Both assistance drill mode.

Figure A

The model includes a hierarchy. The design includes a hierarchy. Initially, the two visualizations seem the same, but looks are tricking. Keep in mind 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 container. The drill buttons are listed below the visualization, and the black circle indicates that drill mode is on.

Figure B reveals what takes place when you click the double-arrow button, which drills to the next level. They’re not the very same, which may be a little bit of a surprise to your end users if they’re used to seeing information in a various order.

Figure B

The visualizations show two different sets of data after drilling down only once. The visualizations show two different sets of information after drilling down just once. The visualization to the right is in nation, group, region order. That indicates the moms and dad levels aren’t the exact same. No matter how completion user drills, the experience will not be the exact same as the one developed on the hierarchy. In truth, the one to the right is a bit misleading because it appears to support the country level as the moms and dad level. We can clearly see by using the hierarchy that the parent level is the group level, at least in the hierarchy.

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

How to add a hierarchy in Power BI

The presentation apply for this Power BI tutorial currently has a hierarchy in the Sales Area table. That hierarchy remains in group, nation, area order, which you saw previously. Let’s add one that’s in nation, group, region order. Most likely, you would not add this 2nd hierarchy, however it’s a fantastic way to compare the differences.

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

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

Figure C

Add a new hierarchy. Include a new hierarchy. 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, select Group. Power BI adds the moms and dad field, Nation, for you.

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

Figure D

Choose the fields in level order. Choose the fields in level order.

Click the Apply Level Changes connect listed below the Area field. When you go back to the Report window, you will find the new hierarchy in the Fields pane, as shown in Figure E. You now have 2 hierarchies on which to build visualizations. A table can have more than one hierarchy, but you ‘d most likely not build 2 that just reorder a number of fields.

Figure E

Access hierarchies via the Fields pane. Gain access to hierarchies through the Fields pane. Users probably will not know you’re utilizing a hierarchy, so the visualizations consistently reveal drill levels in the very same order across all visualizations. However, they may see if you don’t and not in a great way.


Leave a Reply

Your email address will not be published. Required fields are marked *