How to Create a Drill Through Report in Power BI

Building a high-level summary dashboard is great for a quick overview, but what happens when a viewer needs to see the specific details behind a number? You can either redirect them to a different report or, better yet, let them go deeper into the data right from the same dashboard. That's exactly what a drill-through report in Power BI allows you to do.

This article will walk you through what drill-through reports are and exactly how to set them up, step by step. You'll learn how to create interactive and layered reports that let your team explore data without getting lost in clutter.

What is a Drill-Through Report in Power BI?

A drill-through report lets you create a dedicated "destination" page in your report that focuses on a specific entity, like a product, a customer, or a sales region. When a user right-clicks a data point on a "source" page (your main dashboard), they can instantly jump to that destination page. The real value is that the destination page is automatically filtered for the specific data point they selected.

Imagine you have a main dashboard showing total sales by product category. It's a simple bar chart: "Apparel," "Electronics," "Home Goods," etc. With a drill-through, a user could right-click on the "Electronics" bar, choose to "Drill through," and be taken to a new page filled with details only about Electronics sales. They'd see sales trends, top-selling electronic products, and maybe a map of where they're being sold, all filtered down in a single click.

Essentially, it creates a clean, contextual path from a broad overview to a granular analysis.

Drill Down vs. Drill Through: What's the Difference?

People often mix up "drill down" and "drill through" in Power BI. They’re similar but have one key difference:

• Drill Down: This feature works within a single visual. It lets you go down a level in a hierarchy. For instance, you could drill down in a bar chart from "Years" to "Quarters" to "Months" without ever leaving that chart. It expands the visual itself.
• Drill Through: This feature navigates you to an entirely different page. It's about moving from one report context to another, more focused one.

Use drill down for exploring hierarchies, use drill through for jumping to a detailed page about a specific item.

Why Use Drill-Throughs in Your Reports?

Drill-through functionality isn't just a cool trick, it offers practical benefits that make your reports much more effective and user-friendly.

• Reduces Report Clutter: Instead of cramming every possible chart and table onto one massive dashboard, you can keep your main page clean and simple. You can tuck away the detailed views onto secondary pages, making the initial report less intimidating for your audience.
• Provides On-Demand Context: It gives users the power to explore the data that interests them most. If someone has a question about a particular campaign’s performance, they don’t need a separate report. They can get the context they need directly from the source visual.
• Improves User Experience: Instead of applying half a dozen manual filters to get a specific view, users can get there with a single click. This guided navigation makes a huge difference in how people interact with your data.

How to Create a Drill-Through Report: A Step-by-Step Guide

Let's build a simple drill-through report together. We'll stick with our sales example. Imagine our main report page (the "source") has a bar chart showing Total Revenue by Product Category. Our goal is to create a second, hidden page (the "destination") that shows details for whichever category the user selects.

Step 1: Set Up the Destination Report Page

First, we need to create the page that users will drill through to. This is where you configure the logic that tells Power BI, "This page can have data filtered and passed to it."

1. Create a New Page: At the bottom of Power BI Desktop, click the '+' sign to add a new page. Let's rename it "Category Details" for clarity. It’s good practice to make the destination page hidden from regular navigation by right-clicking the page tab and selecting "Hide Page." The only way users will access it is via the drill-through action.
2. Add Your Field to the Drillthrough Well: This is the most important step. On your "Category Details" page, make sure the Visualizations pane is open. Look towards the bottom of the pane for a section named Drillthrough.
3. Drag the drill-through field: From your Data pane, find the field that will be used to filter this page. In our example, it's the Product Category field. Drag the Product Category field and drop it into the box that says "Add drill-through fields here."

Once you drop the field in, two things will happen:

• Keep All Filters Toggle: A switch appears that says "Keep all filters." By default, it's on. This means that if the source visual is already filtered (e.g., by date or country), those filters will also be passed to this destination page. For most cases, you’ll want to leave this on.
• Automatic Back Button: Power BI will automatically add a small "back" button graphic to the top-left of your report canvas. Users will click this to return to the page they came from.

Step 2: Add Visuals to Your Destination Page

With the drill-through filter configured, it’s time to build out your destination page. Remember, this page will always be viewed in the context of one specific category that was passed to it.

On your "Category Details" page, add some useful visuals. For example:

• A Card visual showing the name of the Product Category. Drag the Product Category field to this card. This confirms to the user which category they are viewing.
• A Line Chart showing revenue over time for that category.
• A Table showing the individual products within the category and their individual sales.
• A Donut Chart showing sales by Sub-Category.

Right now, these visuals will show data for all categories. Don’t worry, that’s expected. It will automatically filter when someone uses the drill-through action.

Step 3: Test and Use the Drill-Through from the Source Page

Now for the payoff. Let's go back to our main report page - the one with the bar chart showing sales by category.

1. Navigate back to your main summary page (the "source page").
2. Find the visual you want to drill from, which in our case is the bar chart showing Revenue by Product Category.
3. Right-click on a specific data point. For example, right-click the bar for "Apparel."
4. In the context menu that appears, you will now see an option that says Drill through. Hover over it, and another menu will pop out showing "Category Details" (the name of your destination page).
5. Click on "Category Details."

And that’s it! You'll be taken to your "Category Details" page, and every visual on that page will be filtered to show data only for "Apparel." The card at the top will say "Apparel," the line chart will show Apparel sales trends, and the table will list individual clothing items. Click the back button to return to your main page.

Best Practices and Pro Tips

You’ve got the basics down, but here are a few tips to make your drill-through reports even more powerful and intuitive.

Customize the Back Button

The default back button is functional but plain. You can change its appearance or even use your own image.

Simply click on the button on your destination page to select it, then go to the Format image pane. Here you can change colors, add custom text for the tooltip, or even upload a different image in the Style section to match your company's branding.

Use a Drill-Through Button for a Better UI

Right-clicking isn't always the most obvious action for all users. Power BI lets you create a dedicated button to make the drill-through functionality more visible.

1. On your source page, go to the Insert tab on the ribbon and add a Button. A blank one is great for customization. Place it near the chart it relates to.
2. Select the new button, and in the Format pane, turn on the Action toggle.
3. Set the Type to "Drill through."
4. Set the Destination to your "Category Details" page.
5. Customize the button’s text and state. Under State, you can set the text to something like "Select a category to see details." Change the Disabled state to gray it out, visually telling the user they need to make a selection first.

Now, the button will be disabled until a user clicks on a single category in the bar chart. As soon as they select one, the button will become active, and they can click it to navigate to the detailed page.

Passing Measures to a Drill-Through

You can also create more dynamic destination pages by passing a measure through. If your drill-through field is a text value like a product category, all your visuals are filtered by that category. However, sometimes you want the destination page to focus on a particular metric instead.

To do this, create a measure and add it to your destination page's drill-through well. Then when you right-click on a value in a source chart created with that measure, you can drill through with its filter context.

Final Thoughts

Drill-through reports are one of the most practical features in Power BI for building sophisticated, layered, yet user-friendly dashboards. They allow you to pack a tremendous amount of detail into your reports without sacrificing clarity on your high-level summaries, giving your team the power to explore data themselves.

While mastering features like Drill Through is powerful, we know that the initial setup, data connection, and interface learning curve in tools like Power BI can be time-consuming. We built Graphed because we believe getting deep insights shouldn't require becoming a dashboard configuration expert. Instead of manually building out different report pages and linking them, you can simply ask questions in plain English like, "show me a breakdown of coffee sales in Canada," and get the detailed visualization instantly. Our aim is to automate the busy work, allowing you to focus on the insights, not on the clicks.