Two days ago Microsoft added some new exciting features to Power BI with the new Power BI Weekly Service Update. The more exciting 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”
Select “Sales by Product Category” chart -
From “Fields” pane expand “Products”
-
Drag and drop Product to Axis right behind the Product Category
Now you can see the Drill Down Level icon (
) on the chart
Click on the “Drill Down Level” icon
Voila! -
But, wait! There is more. As you noticed another icon (
) 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 (
) 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.
Click on Bikes
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.
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” (
).
Click on the drill up icon to navigate to the higher level.
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.
It’s just beautiful isn’t it?
Now we can create more exiting data visualisation with drill actions.
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.
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
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.
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