Power BI Drillthrough and Drilldown Explained

Summary pages raise follow-up questions: what is behind that spike, and what exactly did this customer buy? Power BI offers two answers that are easy to confuse. Drilldown moves a single visual through the levels of a hierarchy, such as years to quarters to months. Drillthrough takes the reader to a separate detail page, filtered to the item they selected. Microsoft's documentation keeps them apart for good reason: they solve different problems and are built differently (Microsoft Learn: drillthrough).

Drilldown: moving through a hierarchy

Drill mode works on visuals built from a hierarchy, data organised into nested levels such as Year, Quarter, Month or Category, Subcategory, Product (Microsoft Learn: drill mode). When a visual uses one, up to four icons appear in its header:

The difference between the last three matters. Drilling into one data point, such as the third quarter, focuses the chart on that quarter's months. Going to the next level shows all months, but loses the year labels. Expanding all down keeps both, so months appear grouped under their years.

Making drilldown available

1. Build a hierarchy in the model, for example Year, Quarter and Month in the date table, or Category, Subcategory and Product in the product table. Hierarchies can only use columns from one table, one reason to flatten product dimensions; star schema in Power BI explains why.
2. Put the hierarchy, or several fields in order, on the visual's axis or rows.
3. Leave drill enabled on the visual. If no icons appear, either the visual does not use a hierarchy or drill has been turned off.

Readers often do not notice the drill icons. A subtitle such as "Use the arrows to drill into quarters and months" helps.

Drillthrough: a detail page for one item

Drillthrough is a navigation feature: from a visual showing many customers, a reader right-clicks one customer and opens a page showing only that customer. To build one, following Microsoft's steps:

1. Add a new page and name it for its purpose, such as "Customer Details".
2. In the Visualizations pane, find the Drillthrough section and drag the field that identifies the item, such as Customer Name, into the drillthrough filters well.
3. Power BI adds a Back button automatically, so readers can return to where they came from.
4. Add visuals that make sense for a single item: a card with the customer's name, a trend of their purchases, and a table of their orders.

Any visual on any page that contains the drillthrough field now offers the page in its right-click menu under Drillthrough. You can add more than one field to the well for compound filtering, such as customer and year.

Making drillthrough discoverable

A right-click menu is invisible to readers who do not know it exists. Add a drillthrough button: set a button's action to Drillthrough and choose the target page. The button becomes active once the reader selects a data point, and conditional formatting can change its text, for example to "See details for Acme Ltd". Bookmarks and buttons covers button actions and states in more detail.

Cross-report drillthrough

Drillthrough can also open a page in a different report. Microsoft lists the requirements: both reports in the same workspace, cross-report drillthrough enabled in the target report's settings, and matching field names and data types. It works only in the Power BI service. It suits organisations with a standard detail report, such as a customer profile, that many summary reports should link to.

Which one to use

• Use drilldown when the question is about the same measure at a finer level of time or category: "which months drove this quarter?"
• Use drillthrough when the question needs different visuals about one item: "show me everything about this customer."
• Use tooltips for a quick peek without leaving the page; report page tooltips can show a small chart on hover.

The three work well together: a summary page with drillable charts, tooltips for context, and drillthrough pages for the deep dive.

When drillthrough does not appear

Microsoft's troubleshooting list covers the usual causes:

• The field is missing from the source visual. The drillthrough option appears only on visuals that contain the drillthrough field.
• The field is aggregated in the source visual, for example counted or summed, rather than used as a category.
• The fields do not match, because the source visual uses a differently transformed or duplicated column.
• Relationships block the filter, so the detail page opens but does not filter as expected.
• The button has no filter context, because nothing has been selected yet.

One further quirk: the order of drillthrough pages in the right-click menu can differ between Power BI Desktop and the service.

Design tips for detail pages

• Show which item is selected prominently, in a card or title, so readers never wonder what they are looking at.
• Keep the page focused on one entity, with the visuals a person would need to understand it.
• Hide drillthrough pages from the page tabs if they make no sense without a selection.
• Test with unusual items, such as a customer with one order, to see that visuals remain readable.

Power BI dashboard design covers how detail pages fit into the overall structure of a report.

Drill-down and drill-through reports are part of the data visualisation module of the Power BI course.

Sources

• Use drill mode to explore visuals (Microsoft Learn) — Microsoft Learn
• Drillthrough in Power BI reports (Microsoft Learn) — Microsoft Learn