At some point this weekend, the Microsoft Power BI folks turned on the drill-through feature in the Power BI service. Then the following week, the September release of Power BI Desktop included the new drill-through action feature. This is the same drill-through feature that demonstrated during day 1 of the Microsoft Data Insights Summit, which you can read about here. So I thought it’d be good to quickly walk through how you can set up the drill through action.
First, open a Power BI Desktop file or navigate to a report that you’ve already published to the Power BI service. Then go to the page that you want to use as the target of your drill-through action. Start editing the report page and you’ll notice that on the right side there is a Drillthrough Filters section.
This is where you add fields that will be used to invoke the drillthrough action. I’m going to add the Subject field to the drillthrough filters section, like so.
Once you add the field to the Drillthrough Filters section, a small back arrow will be added to the report to allow you to easily go back to the report page you used to invoke the drillthrough action.
And that’s pretty much it. Now you can invoke the drillthrough action on any other page in the report. To invoke the drillthrough action, right-click a chart that uses the field you added to the Drillthrough Filters section, select Drillthrough, and then select your report. I selected the Life Skills bar in the bar chart and then selected my Drillthrough action.
This automatically takes me to the Subject Dashboard, the target of my drillthrough action, with the correct filter applied, as seen below. My report automatically is filtered to the Life Skills subject!
I’m very excited about this feature in Power BI and I know many of you are, too. I can’t wait to see all the really cool ways people in the Power BI community will use the new Drillthrough Filters functionality. Leave me a comment down below and let me know what you think!
Resources
Download the latest version of Power BI Desktop here.
Read more about the September release of Power BI Desktop.
Watch the Drill-through feature in action in this video.
I have already been using this feature, and it works great! One limitation though: you can only pass through one filter, so this would stop you from creating multiple levels below each other. A workaround: by concatenating the fields that are used as the drillthrough already from the beginning input tables, you can use this concatenated field to pass through ‘multiple’ drillthrough filters.
Example:
Let’s say you have a dataset with countries, cities, streetnames, and the number of people living in a street.
If you would want to drill through to a specific street, this would cause an issue as the city Perth is located both in Australia and the USA, you would end up with streetnames from Perth in both countries. If you, however, create a concatenated column, you get the fields ‘USA-Perth’ and ‘Australia-Perth’. This can then be used as the drillthrough value, making sure you select only the country you really want. 🙂
Thanks for sharing, Stijn. Although this isn’t really a feature limitation. This is more an issue with the data itself.
Unfortunately, this is indeed a feature limitation in my opinion. If I spend a lot of time consuming a high level Dashboard, adjusting slicers and category filters, I would want to see those filters applied to a “branch location” or a “city” that I would inevitably want to see more detail about (with a different set of graphs relative to focused investigation). If those filters no longer exist for the data I’m looking at, I would either not notice at first (which would essentially be incorrect data for what I have in my minds eye), or I would have to re-apply those filters on that next page. I know I don’t want my managers that are using these report dashboards to go through that twice, or pull the wrong set of data unknowingly.
I would love to see multiple filters passed through a drillthrough pointing to an individual location/branch/etc, as you could then create true investigative/reconciliation dashboards. A tremendous value-add!
In the case of the previous comment, this can be done. So it’s not a limitation. You just need to format the data correctly to account for the fact that the data isn’t unique.
Any thoughts on how to prevent users from clicking directly on the tab that is the destination for the drill through rather than using the right-click functionality?
I’m not sure there’s a way to do that. I don’t think you can hide the report page but maybe you could put a note on the report page about how it’s supposed to be used. I’m not sure if there’s a good way to accomplish that.
That was all I could come up with as well. The drill-through functionality was definitely needed but I think they need to enhance it a bit more.
The Power BI engineering team definitely takes the approach of implementing the solution with about 80% of the functionality and then continuing to enhance it and improve it in future dev iterations. The drill through feature is great but I would continue to post suggestions to improve it on ideas.powerbi.com.
Very good written article. It will be supportive to anyone who utilizes it, including me. Keep doing what you are doing – can’t wait to read more posts.
Thank you!
Hello, the drill through feature is really great. However, I have not been able to activate it on my reports that are published to Power BI online and shared with users. Please could you help with how this can be achieved?
Hi Ediri. Take a look at the documentation here on how to turn on the drillthrough functionality: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-drillthrough/ This article is for Power BI Desktop but it works the same way to set it up in the service.
This is actually a big limitation. I want to be able to pass through more than one filter when drilling down. For example, drill down on one visual, say, weekly sales, with the output being different products (on another bar chart). Then I’d like to be able to click on that and drill through to the transaction detail. When I try to do so, I get the detail for ALL TIME, not a subset limited to the weekly sales which I initially filtered by!
I’ve tried to recreate this and have not been successful. I’ve successfully created a drill through from one page to another that passes zip code and country together as separate fields. I’ve done it with a hierarchy in bar chart and as a table. Can you email me a .pbix file that illustrates the issue you’re talking about?
Email sent – thanks!
Hey,
It’s only passing one filter when I try to pass two. Scenario – Bar chart with series and categories . The second filter dropped to drill through filters is ignored.
Shiva
False alarm. Works now.
Whew that was close. 😉
Hi,
Can we do drill through with parameters?
except from drill through filter.
Any other alternate way.
(Question: i want to go one report to other sub-report)
Can you clarify what you mean? Technically the drill through filter feature allows you to pass parameter values from one page to another but are you referring to something different?
Hi Dustin,
I have one master report and four subreport are there. so, I would to go master report to specific subreport. If, I use drill through filter then all subreports names (four subreports) showing in master report ,there will be confusing for end user which subreport should I select.
MY question: Any other alternate way which similar actions like drill through filter ?
Drill through filter can be work tachometer visual?