Drill-down and drill-through in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Drill-Down Behavior

In Power BI, what happens when you use the drill-down feature on a hierarchical chart?

A The chart replaces the current view with the next level of detail for the selected category, hiding the higher levels.

B The chart expands to show the next level of detail within the selected category, keeping the higher levels visible.

C The chart filters to show data only for the selected item, removing all other levels.

D The chart shows a popup with detailed data but does not change the chart view.

💡 Hint

Think about how drill-down changes the chart's level of detail.

Drill-down replaces the current chart view with the next level of detail for the selected category, hiding the higher levels to focus on the detailed data.

❓ dax_lod_result

intermediate

DAX Measure for Drill-Through Filtering

You want to create a DAX measure that counts sales only for the product selected in a drill-through page. Which measure correctly respects the drill-through filter context?

Power BI

Measure = COUNTROWS(Sales)

A Measure = COUNTROWS(Sales)

B Measure = CALCULATE(COUNTROWS(Sales), ALL(Product))

C Measure = CALCULATE(COUNTROWS(Sales), VALUES(Product[ProductID]))

D Measure = CALCULATE(COUNTROWS(Sales), REMOVEFILTERS(Product))

💡 Hint

Drill-through passes filters automatically. Which measure respects current filters?

Using COUNTROWS(Sales) respects the current filter context, including drill-through filters. Using ALL or REMOVEFILTERS removes filters, ignoring drill-through.

❓ visualization

advanced

Best Visualization for Drill-Down Experience

Which visualization type in Power BI provides the best user experience for drill-down on hierarchical data?

A Pie chart with drill-down enabled

B Card visualization showing aggregated value

C Stacked bar chart with hierarchy enabled

D Table visualization without hierarchy

💡 Hint

Consider which chart type naturally supports hierarchical drill-down.

Stacked bar charts support hierarchies and allow users to drill down into categories smoothly, making them ideal for drill-down experiences.

🔧 Formula Fix

advanced

Troubleshooting Drill-Through Filter Not Applying

You created a drill-through page in Power BI but when you drill through, the page shows all data instead of filtered data. What is the most likely cause?

A The data model does not have relationships between tables.

B The source visual does not have any data selected before drilling through.

C The drill-through page has too many visuals causing performance issues.

D The drill-through filter field is not added to the Drill-through filters well on the target page.

💡 Hint

Check the drill-through page filter settings.

Drill-through requires the filter field to be added to the Drill-through filters well on the target page to apply the filter context.

🎯 Scenario

expert

Designing Drill-Down and Drill-Through for Sales Analysis

You have a sales report with a hierarchy: Region > Country > City. You want users to drill down from Region to City and also drill through to a detailed sales transaction page filtered by the selected city. Which setup correctly supports both drill-down and drill-through?

A Create separate charts for each level and link them with bookmarks; no drill-through filters needed.

B Enable hierarchy drill-down on the Region-Country-City chart and add City field to the Drill-through filters on the transaction page.

C Use slicers for Region, Country, and City and disable drill-down; add Region to Drill-through filters only.

D Enable drill-down on the chart but do not add any fields to Drill-through filters; rely on slicers on the transaction page.

💡 Hint

Think about how drill-down and drill-through filters work together.

Enabling hierarchy drill-down allows users to explore data levels. Adding City to Drill-through filters ensures the transaction page filters correctly when drilling through.

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