Drill Action in Power BI

Two days ago Microsoft added  some new exiting features to Power BI with the new Power BI Weekly Service Update. The more exiting one is Drill action in Power BI reports. So from now on we’re able to add a number of fields we’d like to have on the reports with a drill path. That means the users can navigate from a level to another. In this short article I show you how to implement a drill action in Power BI. In the “Data Visualisation with Power BI Desktop” I explained how to implement and publish a complete visualisation in Power BI Desktop. In this post I’m going to use the sample we published to the Power BI website as a sample. So if you are not familiar with how to create data visualisations in Power BI Desktop I encourage you to have a look at this.

Adding Drill Action to Sales by Product Category – Column Chart:

  • Login to your Power BI account
  • Open a report that you want to add a drill action to (as a sample I’m using Adventure Works Reseller Sales which I published previously)
  • Click “Edit Report”Drill Action in Power BI 01
  • Select “Sales by Product Category” chart
  • From “Fields” pane expand “Products”
  • Drag and drop Product to Axis right behind the Product CategoryDrill Action in Power BI 02
  • Now you can see the Drill Down Level icon (image) on the chartDrill Action in Power BI 03
  • Click on the “Drill Down Level” iconDrill Action in Power BI 04
  • Voila!
  • But, wait! There is more. As you noticed another icon (Drill Down in Power BI) also added to the top right of the chart which simply named “Drill Down”. What is does is that it puts the chart on “Drill Mode”. That means when you turn it on the icon changes to (Drill Mode in Power BI) and now you’re able to click on any data points to drill down to the related data in the lower level. So when you turn the “Drill Mode” on and you click on “Bikes” the chart will drill down to Products level under the Product Category.Drill Action in Power BI 05
  • Click on BikesDrill Action in Power BI 06Drill Action in Power BI 07

Adding Drill Action to Sales and Costs by Region – Map:

All the principles are like what we did for the previous chart. So just select the map then drag and drop country on Location, but, this time above the Postal Code.

Drill Action in Power BI 08

As we defined a higher level the map is already drilled down a level to Postal Code, so as you can see the “Drill Down Level” icon changed to “Drill Up” (Drill Up in Power BI).

Drill Action in Power BI 10

Click on the drill up icon to navigate to the higher level.

Drill Action in Power BI 11

Now put the chart on drill down mode then click on a data point, for instance Australia to navigate to the lower level of data related to Australia.

Drill Action in Power BI 12

Drill Action in Power BI 13

Drill Action in Power BI 14

It’s just beautiful isn’t it?

Now we can create more exiting data visualisation with drill actions.

4 thoughts on “Drill Action in Power BI”

  1. While drilling down in the column chart, when I click on the bikes column it’s been drill down to the next level. But while seeing the report which is drilled down, how may I know whether I have cliked Bikes or Clothings or Components or Accessories. Why Power BI is not displaying the value of the data that was clicked in the drilled down report ? Or is there any way to show that ? Please reply.

    1. Hi Meenachi.

      Welcome to biinsight.
      Well, you’re right, it is confusing when you cannot recognize which level you are when you drill down or which column you clicked indeed.
      But, there is workaround, particularly for a Column chart. You can put the top level column into the legend. In that case you have a colour code so can recognize which column you clicked.
      I know this is ugly, but, at the moment I don’t see any easy solution for that.

      Cheers

      1. Hi Sohell, Thanks for your reply. I tried putting the top level column into the legend. It’s working only if you have only one value in the clustered column chart. If you have more than one value in the clustered column chart, it’s not accepting any data field into the legend. Is there any other work around ? Please suggest.

        1. I spent sometime to find an acceptable workaround, but, as per my previous reply there is no easy way to sort it out.
          Power BI is supposed to be easy to implement so taking lots of project time, regardless of the project size, to resolve this doesn’t look feasible to me.
          So I’d rather jump online and vote for the idea already submitted in ideas.powerbi.com.

          Cheers

Leave a Reply