What is Drill Through and How Does it Work?

Internet users are currently generating around 1.7 MB of data per second, and that amount is only set to increase. Brands who learn how to utilize that data have untold access to insights that include consumer habits, preferences, and trends. All of which can help to inform, shape, and prove the value of brand decisions. To take full advantage of this potential, an astounding 62,000 companies worldwide now utilize Microsoft’s Power BI, a business intelligence and reporting platform that creates data visualizations across multiple cloud-based SaaS sources. With Power BI, brands can easily develop and share high-quality data insights, either in-house or with out-house collaborators.

That’s a step in the right direction for companies that are under increasing pressure to put data to good use. But, as data sets become more detailed and thus more nuanced, basic data oversight is no longer enough to ensure success.

For that, brands need the ability to quickly deep dive into all elements of what a data set has to show. Power BI’s Drill Through tool makes that possible at the click of a button.

But what is Drill Through in Power BI, how does it work, and why does that matter in an age of notable data growth?

What is Drill Through in Power BI?

Drill Through is a feature of both Power BI Desktop and Power BI Service that enables the creation of destination target pages based on specific data entities within existing reports. For instance, if a company was dealing with sales data across a set promotional period, drill through would enable the creation of additional click-through links that might outline information including specific store sales, peak performance times, and so on.

Without abandoning the initial report, Drill Through provides additional layers of analysis that can be accessed and hidden depending on their relevance. Drill Through also makes it possible to switch between reports while still analyzing the same data sets. This can lead to a deep level of actionable analysis that allows brands to quickly set data insights against a range of KPIs and requirements. A single Power BI report can then become relevant to everything from sales to marketing KPIs, ensuring that data silos become a thing of the past.

As well as generally making visualizations easier to manage, Power BI Drill Through brings a wide range of benefits to company operations, which include –

• Increased efficiency
• KPI-specific actionable insights
• A broader understanding of departmental data crossovers
• Reports that can be easily explored and understood by third parties
• Reports that provide value across entire organizations
• And more

By effectively furthering the value of insights that Power BI already brings to an organization, Drill Through quite simply enhances what businesses can do with their data, and the efficiency of their outcomes as a result of that. By adding holistic report layers, Drill Through also ensures cross-departmental use, as well as reports that can be reassessed at a later date per things like location-specific sales targets, time-specific promotions, and even the individual performances of each member of a team.

How Does Drill Through Work in Power BI?

Drill Through works by filtering existing report information based on a user’s requirements and input. When setting up their Drill Through report, a user will have the option to select a specific filter from a drop-down menu (e.g. sales data/set time frames), which they can then apply to whatever visualizations they’re working on within this new drillthrough page.

Drill through can be applied in a few different ways, each of which works differently, including –

• Clickable target pages
• Drill through buttons
• Cross Report drill throughs

In each instance, a user will right-click a drill-through link to access new pages within a report, or even to analyze data across different relevant reports as required. Each of these Drill Through variations can be applied across two types of Drill Through filters, one of which can be locked to a report, and the other of which is passed to the target based on the page-level filters of the source page.

How to Add Drill Through in Power BI

The Drill Through feature is readily available to Power BI desktop and service users, and can be applied to an existing master report of your choosing. As mentioned, there are a few different methods and specifics to consider, but for each of them, you’ll first need to add a Drill Through target page. You can do this by following a few simple steps, which are –

1. Create a target report page with relevant visuals for your drillthrough entity
2. Go to visualizations pane > Build Visual
3. Drag your Drill Through field into the Drill Through Well to create a back button visual for your target report.
4. Add Drill Through filters to automatically create a back button visual in your published reports.
5. Use the Insert tab in BI Power Desktop to change your back button visual.
6. Select your new image on the Drill Through target page then go to Format Image >Slider > and select On.
7. Set the type to Back so that your back button links your Drill Through report to your initial target page.
8. If you wish to pass the filters of your main report to your Drill Through, head to the the Drillthrough section of the Visualization Pane, and set the keep all filters option to On.
9. To add measures to your Drill Through area, drag the Drill Through field to the Drillthrough Card. You can then drill through to the page where that field is used in the value area of a visual.

Create a Drill Through Button

It’s also possible to create a Drill Through button in Power BI, which can make a drill through more obvious than a traditional right-click visual would be. This can help to highlight a drill through, and to add notable areas of evidence to, say, a sales-based data analysis report.

To do this –

1. Set up a valid Drill Through page as discussed
2. Create a button with a Drill Through action type, and your drill through page highlighted as the destination.
3. You’ll see two tooltip options for both Enabled and Disabled texts.
4. You can leave these blank, and Power BI will fill them based on your drill through fields, or you can customize them yourself.

Cross-Report Drillthrough in Power BI

The cross-report Drill Through feature makes it possible to contextually jump between two or more reports for an entirely holistic oversight of relevant data sets. This can be a useful tool, though it is worth noting that cross-report drill through doesn’t work in Power BI reports, or in Power BI Report Server.

To initiate a cross-report drill through –

Step 1

1. Select a data point in a source visual and then go to Context menu > Cross-report Drillthrough target to open the target page in the target report.
2. Enable cross-report drillthrough by validating source and target report data models. Do this by ensuring that the names and tables that you want to pass are identical.
3. Go to File > Options and Settings > Options.
4. Select Report Settings in the Current File section.
5. Under Cross-report Drillthrough select Allow visuals in this report to use drillthrough targets from other reports.

Step 2

1. Select the workspace containing your target and source reports.
2. Select the More Options symbol next to the source report name and select Settings.
3. Under Cross-Report Drillthrough select Allow visuals in this report to use drill-through targets from other reports. Click Save.

Cross-report Drillthrough Target Pages

Setting a target page for your cross-report drillthrough is similar to the process required for setting up drill through in the first place.

Set up a cross-report drill through target page in Power BI Desktop or Power BI service as follows –

1. Edit the target file and, on the target page of your report, select Fields in the Visualization pane.
2. Go to Drillthrough and set the Cross-report toggle to On.
3. Drag your required fields into where it says Add drill-through fields here. For each field, select whether you’d like to allow drillthrough when the field is used as a category, or is summarized like a measure.
4. If you don’t want to pass filters between reports, select Off under Keep all Filters.
5. After configuring your target page, Power BI Service users should click save, and Power BI Desktop users should save and publish.

What are Drill Through Fields in Power BI?

Field parameters are a relatively new function in Power BI, and they make it possible for users to toggle between multiple dimensions and values within a data model. These toggles provide complete visualization flexibility and relevance across key focus areas that may include, say, profits across a variety of factors including store locations, audience age ranges, and specific sales periods.

Field parameters have significantly enhanced the value and applicability of Power BI’s reports thanks to benefits that include –

• Increased report customization
• Streamlined data handling
• Interactive reports
• Increased efficiency across previously manual processes

Drill Through fields are simply field parameters that can be directly applied to a drillthrough report. As an example, let’s consider a drill through page that looks at regional activity for a national retailer. Within each regional page, drill through fields could be applied to determine specific details regarding regional branches, including –

• Employee numbers
• Sales figures
• Expense vs profit
• Stock numbers
• And so on

It is possible to pass field filters like profit margins, losses, etc. from your main report to your drillthrough page using the Drillthrough Section of your main report’s Visualization Panel and toggling the Keep all filters option to On.

If you wish to create separate drill through fields, you can select Off, and set any fields separately by –

1. Click on the Modeling tab in the ribbon at the top of the screen.
2. Select New Parameter from the dropdown menu.
3. Enter a name and value for your parameter
4. Select a data type for your parameter (text, whole number, data, etc.)
5. Click OK.

Use Cases for Drill Through in Power BI

As we’ve already covered, Drill Through is a feature of Power BI that can pack a valuable data punch across industries, and there are a fair few use cases that make it more than worthwhile to get to grips with this valuable tool.

A few of the most obvious use cases that should you reaching for the Drill Through tab include –

• Holistic KPI oversight: Setting separate Power BI visuals for entire company KPIs can be time-consuming, convoluted, and difficult to correlate. By making it possible to ‘drill through’ all of your data sets, drill through pages can ensure holistic KPI oversight that tackles everything from sales targets to marketing drives in one neat report.
• Increased sales power: Power BI visuals are a powerful sales tool, and drill through pages take that benefit even further thanks to segmented, inclusive data sets that ensure you can easily answer even niche buyer queries, like region-specific data, and sales in certain periods. All of which can help you to look more knowledgeable, and ensure that you’re able to highlight every relevant area of your success on-screen.
• Smoother operations: By keeping even cross-departmental data in one place, and even highlighting correlations with individual drill throughs that apply to each team, drill through usage can significantly simplify operations that, previously, would have seen your employees having to access multiple reports, and identify data correlations manually.

Double Your Drill Through Efficiency With PBRS

Power BI Drill Through simplifies how you handle and analyze your data sets. PBRS, our SSRS and Power BI Reporter Scheduler, can perfectly complement those benefits by providing brand-new levels of control over your insights.

PBRS easily integrates with Power BI and makes it possible to merge your reports to inform drill through pages, or draw correlations across a collaborative bank of services that significantly broadens your data horizons. With event-triggered reporting and automatic distribution to relevant parties, PBRS can also ensure that your drill through pages are relevant, well-seen, and more likely to lead to improvements.

Whether you’re using drill throughs for your KPIs, sales pitches, or both, PBRS can add clout to your Power BI case. But, you don’t need to take our word for it. Book your free PBRS trial today, and see the results for yourself!