Microsoft Power BI is a data visualization platform that makes it possible to perform business intelligence for a much improved decision-making. You can connect data from various data sources and visualize your data by building interactive reports or dashboards.
With interaction in reports, it’s easier to understand, track, and manipulate complex data. There are different ways to achieve interactive filtering but we’ll only focus on drill through here. In this guide, we’ll explain how to set up and use drill through reports in Power BI.
What is Drill Through in Power BI?
Drill through is an interactive filtering feature that allows report consumers to expand on some data items. An overview report page is meant to contain a summary of all your datasets so it’s advised to create other pages for the breakdown of certain categories. It makes it possible for report consumers to drill from a visual to another report page. The action also connects all filters that apply to the visual to the drill through page. Power BI drill through helps to keep your report organized and improves the report experience.
Example of Drill Through in Power BI
In case you haven’t seen drill through before, we’d like to show what drill through actually looks like. First, you’d have a main page that contains the primary visuals for your data. Here’s a contact page featuring a slicer visual for the First Name data field.
When you right click on each first name, you should see a dropdown with a list of actions. If drill through is working, you’ll see the target page name when you hover.
The contact details page has specific information about each contact. This is very useful in a situation where you have to summarize multiple aspects of your data on a page. It allows you to simply use a drill through to explore the required details on another page.
In the next chapters, we’ll provide a step-by-step tutorial on how you can add and set up drill through in Power BI.
How to set up Drill Through in Power BI?
Drill through allows you to navigate from one report page to another. You just have to pick a data point that needs to be explored, design the standalone page and connect. It can be done with just a couple of clicks on either Power BI service or Power Bi desktop. Before we describe the process in full, here’s a list of the main steps for drill through:
- Connect your data source using manual or automated integrations
- Create the source report page for the visuals you want to drill through
- Select a particular visual or data point for the drill through
- Create the drill through target report page and apply necessary filters
- Connect the two pages – drag the data field to the drill through section in the “Visualizations” pane of the target report page
- Return to the source page and right-click to see if it navigates to the target page.
Now, let’s go through this in more detail.
How to add drill through in Power BI: a detailed explanation
It’s important for you to learn how to use drillthrough in Power BI reports. In this section, we’ll use Power BI desktop to show how to get it done without much trouble. Below, there are steps for connecting your data source and adding drill through to report pages:
1. Get the Integration URL to connect your data source to Power BI
The first step when creating a report is to connect your data to Power BI. Natively offered Power BI data sources include files (CSV, Excel, PDF etc), databases, Power Platform, Azure, Microsoft Fabric, and online services. You can easily connect to each data source in these categories when building reports. But you may also want to consider automating your data with a third-party platform.
In our example, we will use Coupler.io to transfer data to Power BI.
“Coupler.io is a data automation platform that allows you to easily gather, transform, and send data to Power BI, Looker Studio, and Tableau. It can automatically import data to your destination on a custom schedule and update your report every 15 minutes. In this way, Coupler.io can power your self-updating dashboards and reports in Power BI.”
Connecting data with Coupler.io is very easy. When you are logged in to your account, just select the apps you want to connect. You can choose between 50+ data sources and several destinations.
- Then just follow the instructions on the screen to connect your accounts and extract data.
- Below you see a preview of a sample dataset extracted from QuickBooks.
In a minute we will load this data into Power BI and will be able to start working with it.
- Copy the Integration URL – you will need it to connect your dataset to Power BI.
Just one little thing left – we want our dashboard in Power BI to be updated automatically. This way, it will always display fresh data pulled from QuickBooks on a schedule. So we toggle on Automatic data refresh and set up a custom schedule for next imports.
When you click Save and Run, you’ll be taken to the importer page. If the import was successful, you should see a notification box at the top that says “Import successful”. The importer page contains the integration URL and allows you to turn off or edit the schedule settings for automatic data transfers into your destination account.
2. Connect your data and create the source and target pages
In this chapter, we’ll use the integration URL from Coupler.io to connect our data (we’ve explained in the previous section how to get it). Next, we’ll create the source and target page for drill through and link them to each other.
- Open a new report. You’ll be asked to add data to your report.
- Click Get data from another source. Choose Other > Web and click Connect.
- Paste your integration URL into the box and click OK.
In the next pop-up, you’ll choose the level to apply the settings to and click Connect. You should be able to find your datasets already aggregated in Power Query Editor.
- Design the source report page. It can be something like this:
- Add bar charts or table visual to the destination page too.
3. Link both pages together with drill through
Now, let’s link the pages that we’ve just created. In Power BI, there’s a Data pane that has a list of fields and the Visualizations pane with a drill through section. Go to the target page and drag the desired data field from the Data pane to the Drill through section where it says “ Add drill through fields here”.
- Return to the main page to test the drill through functionality. When you hover on the chosen data point, you should see a message such as the one below.
- Right-click on the data point to ensure the drill through works as intended.
When you click, it should take you to the destination page. The main reason for using drill through is to provide detailed information on a data point albeit on a separate report page. It allows consumers to view additional and supporting data when needed.
What are Drill Through fields in Power BI?
Drill through is an interactive feature that allows report consumers to navigate from a summary or overview report to detailed report pages by right-clicking on a data point. A drill through field helps to filter the detailed report and show the information pertaining to that data field. You can add drill through fields in Power BI to explore breakdown of specific visuals or data points.
Here’s an image of drill through fields that have been added to a Power BI report.
When you drag the data field to the drill through section, it automatically allows the user to drill through to the next page when any of the entities have been selected. For example, you can right-click on Amarilla wherever the “product” data point is found, and you’ll be navigated to a destination page that displays more information or KPIs concerning the Amarilla product. This provides the opportunity to focus on each data entity and analyze each of them separately.
Note: Power BI automatically creates a back button visual to return to the source page. It becomes a button in the published report and you can also use your own image for it.
Power BI – clear Drill Through Filters
Drill through in Power BI helps to navigate the end user to a different page in the same report or to another report, i.e. cross-report drill through. It ensures that you do not clutter a report page with too many details. Rather, you can click to get these details when required.
If you want to clear drillthrough filters, all you have to do is click on the x symbol next to each field name to remove the filter. You’d repeat to completely clear multiple fields. You can return to the source page to check if the drill through action is no longer active.
What to do if Power BI Drill Through is not working?
Power BI drill through may not work if you added the wrong drillthrough filters. You have to know the data point and the target page it should lead to. If you notice that Power BI drill through isn’t working after adding the right filters, you should clear them and start over. Else, you should save your report, close Power BI, and open it again.
Power BI Drill down vs Drill Through
Power BI has interactive features that let you organize data in a way that shows the relationship of general data to more comprehensive details. The most popular filtering options in Power BI reports are drill down and drill through. The first one, drill down, is an analytical feature that allows report consumers to visualize lower levels of a data point by nesting additional variables within the same chart or visualization.
You can add all your data to a report and only reveal them when the drill down button is clicked. It allows you to hide and show other report items when needed. Drill down in Power BI allows you to understand data and extract insights without leaving the page.
On the other hand, the drill through feature in Power BI allows you to visualize detailed data about a specific data point or KPI in a separate report. Although drill down and drill through are quite similar, they can’t replace each other. It’s best to use the drill down feature when you need to provide extra details within a chart or data set. But Power BI drill through is preferred when you’d like to explore other relevant aspects of the data.
Automate data exports to Power BI with Coupler.io
Get started for freeSummary: How To Use Drill Through in Power BI?
In summary, drill through filters help the report consumer to analyze specific data or explore more information on another page. It ensures that your Power BI report remains organized and easy for anyone to navigate. The report consumer would view the main report page, pick a visual element they’re interested in, right-click to drill through, perform analysis on the target page, and return to the source page when done.
We also discussed data automation through Coupler.io and showed how to set up the importer. Once you integrate your data, all you need to do is add the integration URL to Power BI. Then, you can create report pages and include the appropriate visuals on them. Apart from the direct Power BI integration, the Coupler.io data team also offers template reports and dashboards so you can plug in your data and analyze faster.
To learn more about using Power BI, see our Power BI tutorial.
