Drill-down and drill-through in Power BI - Step-by-Step Guide

Introduction

Drill-down and drill-through let you explore data in more detail. Drill-down helps you see smaller parts inside a chart. Drill-through takes you to another page with related details. These features make reports interactive and easier to understand.

When you want to see sales by year and then by month without making many charts

When you need to explore product categories and then see individual products in the same chart

When you want to click on a region in a map and see detailed sales data for that region on another page

When you want to keep your report clean but allow users to find more details on demand

When you want to analyze customer segments and then drill through to see customer profiles

Steps

Step 1: Open your Power BI report

- Power BI Desktop main window

Your report with visuals is visible on the canvas

Step 2: Add a hierarchy to your visual for drill-down

- Fields pane, drag multiple fields into the Axis area of a chart visual

The chart shows data grouped by the top-level field with a drill-down icon

Step 3: Click the drill-down button (down arrow) on the visual header

- Visual header of the chart

The chart zooms into the next level of data, for example from year to month

Step 4: Create a new page for drill-through details

- Bottom tabs, click the + icon to add a new page

A blank report page appears for detailed data

Step 5: Add fields to the Drill-through filters well

- Visualizations pane, Drill-through filters section on the new page

The page is set to receive drill-through filters from other pages

Step 6: Go back to the main report page and right-click a data point in a visual

- Any visual with data related to the drill-through field

A menu appears with the option to drill through to the detail page

Step 7: Select the drill-through page from the menu

- Right-click menu on the data point

Power BI navigates to the detail page filtered by the selected data

Before vs After

Before

A bar chart shows total sales by year only, no way to see months

After

The same bar chart allows clicking a year to drill down and see sales by month

Settings Reference

Drill-down button

📍 Visual header of a chart visual

Enable or disable drill-down interaction on the visual

Default: Off

Drill-through filters

📍 Visualizations pane on the drill-through page

Set fields that the drill-through page will filter by

Default: None

Visual header icons

📍 Format pane > Visual header

Control visibility of drill-down and drill-up buttons on visuals

Default: Show

Common Mistakes

Not adding multiple fields to create a hierarchy for drill-down

Without a hierarchy, drill-down cannot work because there is no next level to explore

Drag multiple related fields into the Axis area to build a hierarchy before enabling drill-down

Not adding fields to the Drill-through filters well on the detail page

The drill-through page will not filter correctly and show unrelated data

Always add the field you want to filter by to the Drill-through filters well on the target page

Trying to drill-through from a visual that does not contain the drill-through field

The drill-through option will not appear in the right-click menu

Ensure the visual includes the field used in the drill-through filter

Summary

Drill-down lets you explore data levels inside one visual by clicking to see more detail.

Drill-through lets you jump to another report page filtered by the selected data point.

You must set up hierarchies for drill-down and add fields to Drill-through filters for drill-through to work.

Practice

1. What is the main difference between drill-down and drill-through in Power BI?

easy

A. Drill-down explores data levels within the same visual, drill-through navigates to another page.

B. Drill-down creates new visuals, drill-through deletes existing visuals.

C. Drill-down filters the entire report, drill-through filters only one visual.

D. Drill-down exports data, drill-through imports data.

Solution

1. Step 1: Understand drill-down functionality

   Drill-down lets you explore different levels of data hierarchy inside the same visual, like going from year to month.

2. Step 2: Understand drill-through functionality

   Drill-through takes you to a different report page filtered by the selected data point, focusing on details.

3. Final Answer:

   Drill-down explores data levels within the same visual, drill-through navigates to another page. -> Option A

4. Quick Check:

   Drill-down = same visual levels, Drill-through = different page [OK]

Hint: Drill-down stays on page; drill-through jumps to another page [OK]

Common Mistakes:

• Confusing drill-down with drill-through navigation
• Thinking drill-down changes pages
• Assuming drill-through modifies the same visual

2. Which of the following is the correct way to enable drill-through on a report page in Power BI?

easy

A. Use the 'Export Data' option on the source visual.

B. Create a new visual and enable drill-down in its format settings.

C. Add a drill-through filter field to the Drill-through filters pane on the target page.

D. Add a slicer to the source page and link it to the target page.

Solution

1. Step 1: Identify drill-through setup

   To enable drill-through, you must add a field to the Drill-through filters pane on the target page to receive filter context.

2. Step 2: Check other options

   Options B, C, and D do not correctly enable drill-through functionality.

3. Final Answer:

   Add a drill-through filter field to the Drill-through filters pane on the target page. -> Option C

4. Quick Check:

   Drill-through filter field setup = Add a drill-through filter field to the Drill-through filters pane on the target page. [OK]

Hint: Drill-through needs filter field on target page filters pane [OK]

Common Mistakes:

• Trying to enable drill-through on source page instead of target
• Confusing drill-down format settings with drill-through setup
• Using slicers instead of drill-through filters

3. Given a bar chart with Year > Quarter > Month hierarchy enabled for drill-down, what happens when you click the drill-down button and then select a year bar?

medium

A. The report navigates to a new page filtered by the selected year.

B. The chart shows quarters for the selected year within the same visual.

C. The chart exports data for the selected year.

D. Nothing happens because drill-down is disabled.

Solution

1. Step 1: Understand drill-down behavior on hierarchy

   Clicking drill-down and selecting a year drills into the next level, showing quarters for that year in the same visual.

2. Step 2: Eliminate other options

   The report navigates to a new page filtered by the selected year. describes drill-through, not drill-down. The chart exports data for the selected year. is unrelated. Nothing happens because drill-down is disabled. contradicts the question setup.

3. Final Answer:

   The chart shows quarters for the selected year within the same visual. -> Option B

4. Quick Check:

   Drill-down moves down hierarchy in same visual [OK]

Hint: Drill-down moves inside visual hierarchy levels [OK]

Common Mistakes:

• Confusing drill-down with drill-through navigation
• Expecting page change on drill-down
• Ignoring hierarchy levels in drill-down

4. You set up a drill-through page but clicking the drill-through option on the source visual does nothing. What is the most likely cause?

medium

A. The drill-through filter field is missing on the target page.

B. The source visual does not have any data points.

C. The drill-down button is enabled instead of drill-through.

D. The report page is not published.

Solution

1. Step 1: Check drill-through setup requirements

   Drill-through requires a filter field on the target page to receive context from the source visual.

2. Step 2: Analyze other options

   The source visual does not have any data points. might affect visuals but not drill-through availability. The drill-down button is enabled instead of drill-through. confuses drill-down with drill-through. The report page is not published. is unrelated to drill-through functionality.

3. Final Answer:

   The drill-through filter field is missing on the target page. -> Option A

4. Quick Check:

   Missing drill-through filter field blocks drill-through [OK]

Hint: Always add drill-through filter field on target page [OK]

Common Mistakes:

• Confusing drill-down and drill-through buttons
• Not adding filter field on target page
• Assuming drill-through works without data

5. You want to create a report where users can drill-down from Region to Country to City in a map visual, and also drill-through to a detailed sales page filtered by City. Which setup is correct?

hard

A. Create separate visuals for each level and link them with bookmarks.

B. Add City as a slicer on the main page and enable drill-down on the detailed page.

C. Use drill-through filters on the map visual and disable drill-down.

D. Enable hierarchy drill-down on the map visual and add City as a drill-through filter on the detailed page.

Solution

1. Step 1: Setup drill-down hierarchy on map visual

   Enable hierarchy drill-down from Region to Country to City so users can explore levels inside the same visual.

2. Step 2: Setup drill-through filter on detailed page

   Add City as a drill-through filter on the detailed sales page to allow navigation filtered by City.

3. Step 3: Confirm other options are incorrect

   Add City as a slicer on the main page and enable drill-down on the detailed page. misplaces drill-down on detailed page. Use drill-through filters on the map visual and disable drill-down. disables drill-down incorrectly. Create separate visuals for each level and link them with bookmarks. uses bookmarks, which is not drill-down or drill-through.

4. Final Answer:

   Enable hierarchy drill-down on the map visual and add City as a drill-through filter on the detailed page. -> Option D

5. Quick Check:

   Drill-down + drill-through setup = Enable hierarchy drill-down on the map visual and add City as a drill-through filter on the detailed page. [OK]

Hint: Combine drill-down hierarchy and drill-through filter for best UX [OK]

Common Mistakes:

• Confusing where to enable drill-down vs drill-through
• Not adding drill-through filter on target page
• Using bookmarks instead of drill-through