Drill mode in a visualization in Power BI

Drill requires a hierarchy

When a visual has a hierarchy, you can drill down to reveal additional details. For example, you might have a visualization that looks at Olympic medal count by a hierarchy made up of sport, discipline, and event. By default, the visualization would show medal count by sport -- gymnastics, skiing, aquatics, and so on. But, because it has a hierarchy, selecting one of the visual elements (such as a bar, line, or bubble), would display an increasingly more-detailed picture. Select the aquatics element to see data for swimming, diving, and water polo. Select the diving element to see details for springboard, platform, and synchronized diving events.

You can add hierarchies to reports you own, but not to those shared with you. Not sure which Power BI visualizations contain a hierarchy? Hover over a visualization and if you see these drill controls in the top corners, your visualization has a hierarchy.

Dates are an unique type of hierarchy. When you add a date field to a visualization, Power BI automatically adds a time hierarchy that contains year, quarter, month, and day. For more information see Visual hierarchies and drill-down behavior or watch the video below.

Note

To learn how to create hierarchies using Power BI Desktop, watch the video How to create and add hierarchies

Prerequisites

  1. In Power BI service or Desktop, drill requires a visualization with a hierarchy.

  2. To follow along, open the Retail Analysis sample and create a treemap that looks at Total Units This Year (Values) by Territory, City, PostalCode, and Name (Group). The treemap has a hierarchy made up of territory, city, postal code, and city name. Each territory has one or more cities, each city has one or more postal codes, and so on. By default, the visualization displays only the territory data, because Territory appears first in the list.

  3. Understanding how the various drill icons work together can be confusing, so let's filter the treemap to show only 2 of the smaller territories: KY and TN. Select the treemap and under Visual level filters expand Territory and select KY and TN.

    filter for KY and TN

    Now only two territories display in the treemap.

    double drill icon

Three ways to access the drill features

You have several options for accessing the drill down, drill up, and expand features for visualizations that have hierarchies. This article shows you how to use the first option below. Once you understand the basics of drill down and expand, all three of these methods accomplish the same things, try them out and pick the one that you enjoy the most.

  • Hover over a visualization to see and use the icons.

    drill pathway

  • Right-click a visualization to reveal and use the menu.

    contextual menu

  • From the Power BI menu bar, select the Explore button.

Drill pathways

Drill down

You have several ways to drill into your visualization. Drill down takes you to the next level in the hierarchy, so if you're looking at the Territory level, you can drill down to city level then postalcode level and finally name level. Each step in the path shows you new information.

drill pathway

Expand

Expand adds an additional hierarchy level to the current view. So if you're looking at the Territory level, you can expand and add city, postalcode,and name detail to your treemap. Each step in the path shows you the same information and adds on one level of new information.

expand pathway

You can also choose whether to drill down or expand on one field at a time or on all fields at a time.

Drill down all fields at a time

  1. Start at the top level of the treemap showing data for KY and TN. Widen your treemap by selecting one of the handles and dragging to the right.

    treemap showing 2 states .

  2. To drill down all fields at once, select the double arrow in the upper-left corner of the visualization double drill down icon . Your treemap now shows city data for Kentucky and Tennessee.

    double drill icon

  3. Drill down one more time to the postalcode level of the hierarchy.

    double drill icon

  4. To drill back up, select the up arrow in the upper-left corner of the visualization .

Drill down one field at a time

This method uses the drill down icon that appears in the top right corner of the visualization itself.

  1. Select the drill down icon to turn it on drill down turned on. Now you have the option of drilling down one field at a time.

    If you don't turn on drill down, selecting a visual element (such as a bar or bubble or leaf) will not drill down but will instead cross-filter the other charts on the report page.

  2. Select the leaf for TN. Your treemap now displays all the cities in Tennessee that have a store.

  3. At this point you can continue drilling down for Tennesee or you can drill down for a particular Tennesee city or you can expand instead (see Expand all fields at once, below). Let's continue to drill down one field at a time. Select Knoxville, TN. Your treemap now displays the postalcode for your store in Knoxville.

    Notice that the title changes as you drill down and back up again.

Expand all and expand one field at a time

Having a treemap that shows us only a postalcode isn't very informative. So let's expand down one level in the hierarchy.

  1. With the treemap active, select the expand down icon expand down icon . Your treemap now shows 2 levels of our hierarchy: postalcode and store name.

    showing postalcode and store name

  2. To see all 4 hierarchy levels of data for Tennesee, select the drill up arrow until you reach the second level, Total units this year by territory and city, of your treemap.

  3. Make sure drill down is still turned on drill down turned on , and select the expand down icon expand down icon. Your treemap now shows some additional detail; instead of only showing city and state it now also shows us postalcode.

    double drill icon

  4. Select the expand down icon one more time to display all 4 hierarchy levels of detail for Tennesee on your treemap. Hover over a leaf to see even more detail.

    treemap showing Tennesee data

Drilling filters other visuals

As you work in Drill mode, you get to decide how drill down and expanding impacts the other visualizations on the page.

By default, drilling will not filter other visuals in a report. But this feature can be enabled in Power BI Desktop and Power BI service.

  1. In Desktop, select the Format tab and select the checkbox for Drilling filters other visuals.

    setting in Power BI Desktop

  2. Now when you drill down (or drill up or expand) in a visual with a hierarchy, that action filters the other visuals on the page.

    setting in Desktop

    setting in Desktop

Note

To enable this in Power BI service, from the top menubar, select Visual interactions > Drilling filters other visuals.

setting in Power BI service

Understanding the hierarchy axis and hierarchy group

You can think of the hierarchy axis and the hierarchy group as the mechanisms that you can use to increase and decrease the granularity of data you want to view. Any data that can be organized into categories and subcategories qualifies as having a hierarchy. This, of course, includes dates and times.

You can create a visualization in Power BI to have a hierarchy by selecting one or more data fields to add to either the Axis well or the Group well, along with the data you want to examine as data fields in the Values well. You'll know if your data is hierarchical if Drill mode icons appear on the upper-left and right corners of your visualization.

Essentially, it's convenient to think of two types of hierarchical data:

  • Date and time data - If you have a data field with a DateTime data type, you already have hierarchical data. Power BI automatically creates a hierarchy for any data field whose values can be parsed into a DateTime structure. You only need to add one DateTime field to the Axis or Group well.
  • Categorical data - If your data is derived from collections that contain sub-collections, or otherwise has rows of data that share common values, you have hierarchical data.

Power BI enables you to expand by one or by all subsets. You can drill down through your data to see a single subset at each level, or to see all of the subsets simultaneously at each level. For example, you can drill down for a specific year, or see all of the results for each year as you go down the hierarchy. Conversely, you can drill up in the same manner.

The following sections describe drilling down from the highest view, middle view, and lowest view.

Hierarchical data and time data

For this example, follow along with the Retail Analysis sample and create a stacked column chart visualization that looks at Month (Axis) by TotalSales (Values).

Even though the Axis data field is Month, it still creates a Year category in the Axis well. This is because Power BI provides the full DateTime structure for all the values it reads. The top of the hierarchy shows data for the year.

With Drill Down mode on, click the bar in the chart to go down one level of the hierarchy. You will see three bars for the data of the quarters available. Then from the upper left icons, choose Expand all down one level of the hierarchy. Then do that again to get to the lowest level of the hierarchy, that shows results for each month.

Apart from the visualization, we can see the hierarchy reflected in the data rendered for each report. The following table shows the results of Show Data in a report drilling down from a single month or all months.

Notice that the data is the same for quarter and year reports, but after you drill down to the level of detail specified for Values, you can see how the single report gets more specific and the "all months" report has more data.

Expand mode Year Quarter Month Day
Single
All

Hierarchical category data

Data that has modeled from collections and sub-collections is hierarchical. A good example of this is location data. Consider a table in a data source whose columns are Country, State, City, and Zip. Data that shares the same Country, State, and City are hierarchical.

For this example, follow along with the Retail Analysis sample. Create a stacked column chart visualization that looks at Total Units This Year (Values) by Territory, City, PostalCode, and Name (Group).

With Drill Down mode on, from the upper left icons, choose Expand all down one level of the hierarchy three times. You should be at the lowest level of the hierarchy, which shows the results for Territory, City, and Postal Code.

Apart from the visualization, we can see the hierarchy reflected in the data rendered for each report. The following table shows the results of Show Data in a report drilling down for a single territory or all territories. As you drill down, you can see how the single report gets more specific and the "all territories" report has more data.

Expand mode Territory City Postal Name
Single
All

Considerations and limitations

  • If adding a date field to a visualization does not create a hierarchy, it may be that the "date" field is not actually saved as a date. If you own the dataset, open it in Data view in Power BI Desktop, select the column that contains the date, and in the Modeling tab, change the Data Type to Date or Date/Time. If the report has been shared with you, contact the owner to request the change.

Next steps

Visualizations in Power BI reports

Power BI reports

Power BI - Basic Concepts

More questions? Try the Power BI Community