Technology

Drill Down Feature In Power BI

Power BI drill down feature requires 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 the sum of sales of the products by a hierarchy made up of Name column of cities and ModifiedDate column. By default, the visualization displays only the Name data, because it appears first in the list. The visualization and hierarchy are shown in the following pictures.

        

Because it has a hierarchy, selecting one of the visual elements (such as bar, line or bubble), would display an increasingly more detailed picture.

Select the Name column of cities to see data for Sales. Select the ModifiedDate column to see data for ModifiedDate in the report.

I have selected the year 2011 to see the more detailed picture of this year’s sales. This is only possible after the drill down functionality is enabled. Two methods are discussed below in this article which explained how to work with dill down functionality of Power BI.

               Before Selection                                              After Selection

 

You can also add new hierarchies in your report, to do that you can drag a new column in the group field. If you do not know which visualizations contain hierarchy? Hover over a visualization and if you see these drill controls in the top corners, your visualization has a hierarchy.

Dates are a 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.

Methods to drill down

There are 2 different way to drill down (and up) in your visualizations. Both methods are discussed in this article and they both achieve same results. You can use the method you would like most.

Method 1 uses the drill icons that appear in the top corners of the visualization itself.

To enable drill down, select the arrow icon in the top right corner of the visualization. When the icon is dark, the drill is enabled. That icon is highlighted in the following picture.

                  Not Enabled                                     Enabled

To drill down one field at a time, click one of the elements in your visualization, in a bar chart. Notice that the title changes as you drill down and back up again. To drill up select this icon in the upper right corner of the visualization.

To drill down all fields at once, select the double arrow in the top left corner of the visualization.

Method 2 uses the Data/Drill tab from the top Power BI menu bars.

To enable drill down, select a visualization to make it active and from the Power BI top menu bars. select Data/Drill > Drill Down. The drill-down icon in the top right corner of the visualization changes to a black background.

To drill down all fields at once, select Data/Drill > Show Next Level.

To drill back up, select Drill / Data > Drill Up.

Considerations

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 have owned the data set, 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 as shown in the picture below. If the report has been shared with you, contact the owner to request the change.

After that, the drill down feature will work fine.

Author

Hassan Askari