Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Drill Down and Drill Through

Power BI provides interactive exploration features that allow users to move from summarized information to more detailed business data. Drill down helps users explore different levels within a hierarchy, while drill through opens a separate report page containing details related to a selected data point.

These features are useful when a manager wants to investigate why sales changed, which products contributed to a result, or which transactions belong to a particular customer or region.

Learning Objectives

  • Understand the difference between drill down and drill through.
  • Create and use hierarchies in Power BI visuals.
  • Navigate from higher-level data to lower-level details.
  • Create and configure a drill-through report page.
  • Use drill-through filters and return navigation.
  • Identify common problems with drill interactions.

1. What Is Drill Down?

Drill down allows a user to move from a higher level of a hierarchy to a more detailed level within the same visual. For example, a chart can display sales by year. Drilling down can reveal quarters, months, and individual dates when the hierarchy is configured accordingly.

Drill-Down Hierarchy

Year: 2026 Quarter: Q1, Q2, Q3, Q4 Month: January, February, March Day: Individual dates

Each level provides more detail than the level above it. The exact hierarchy depends on the fields used in the visual.

2. What Is a Hierarchy?

A hierarchy organizes related fields into levels. Power BI can use built-in date hierarchies or fields arranged into a custom hierarchy. A hierarchy gives users a structured way to move between summary and detail levels in supported visuals.

Example: Date Hierarchy

Level Example Field Information Displayed
Level 1 Year Total sales for each year
Level 2 Quarter Sales for each quarter
Level 3 Month Sales for each month
Level 4 Date Sales for individual dates

Example: Geographic Hierarchy

A geographic report might organize data using Country, State, City, and Store. A user can begin with country-level results and explore progressively more detailed geographic information.

Important: Drill down requires a hierarchy or multiple levels in the visual. A chart containing only one category field does not automatically provide additional levels to explore.

3. Add a Hierarchy to a Visual

Before using drill down, configure a visual with the fields you want users to explore.

Step-by-Step Instructions

  1. Open Power BI Desktop and load your sales data.
  2. Insert a column chart or another visual that supports hierarchical exploration.
  3. Add a date field or the first hierarchy level to the visual's category axis.
  4. Add the next hierarchy levels, such as Quarter, Month, and Date.
  5. Add a measure such as Total Sales to the value field.
  6. Review the drill controls available in the visual header.
  7. Use the controls to explore the hierarchy.

If your data does not contain a suitable date hierarchy, you can create separate Year, Quarter, Month, and Date columns in your date table and arrange them into a hierarchy where appropriate.

Check your date fields: Month names alone may sort alphabetically. Use a month number or an appropriate date-table sorting column to display months in calendar order.

4. Drill-Down Controls

Power BI visuals can provide different controls for navigating hierarchies. Their exact appearance and availability can vary by visual type and interface version.

Control or Action Purpose
Drill down Explore the next hierarchy level for a selected data point.
Go to the next level Display the next hierarchy level across the visual.
Expand all down one level Show the next level while retaining the current grouping structure where supported.
Drill up Return to a higher hierarchy level.

Drill Down Versus Drill Up

Year View Summary results Drill down Month View Detailed results Drill up Back to Summary Higher level

5. Explore a Specific Data Point

Drill down on a specific data point focuses exploration on the selected branch of a hierarchy. For example, when viewing sales by year, you might select 2026 and explore its quarters or months.

Example Workflow

  1. Display Total Sales by Year in a column chart.
  2. Enable drill-down mode using the visual header control.
  3. Select the bar representing 2026.
  4. Review the quarters or next available level for that year.
  5. Select a quarter to explore its months if another level exists.
  6. Use Drill up to return to the year-level view.
Business Use: This is helpful when a manager sees that annual sales have decreased and wants to identify which quarter or month contributed to the change.

6. What Is Drill Through?

Drill through allows a user to navigate from a data point on one report page to another report page designed to show related details. The destination page receives the relevant drill-through filter context when the navigation is configured correctly.

For example, a summary page may display sales by product category. A user can right-click a category and open a detail page containing product names, quantities, sales values, and other relevant fields.

Drill-Through Navigation

Sales Summary Page Sales by Category Selected Drill through Product Detail Page Category: Electronics Laptop $4,500 Monitor $1,800 Keyboard $600

The destination page displays details filtered to the selected category, provided the source field and destination drill-through configuration are compatible.

7. Drill Down vs. Drill Through

Feature Drill Down Drill Through
Navigation Explores levels within a visual. Opens a different report page.
Requirement A hierarchy or multiple levels in a supported visual. A destination page configured with drill-through fields.
Main purpose Move from summary to a lower hierarchy level. View related detail information on another page.
Example Year to quarter to month. Category summary to product details.
Typical interaction Use visual header drill controls or select a data point in drill mode. Right-click a data point and choose a drill-through destination.
Remember: Drill down and drill through are different operations. Drill down explores a hierarchy; drill through navigates to a separate page with related filter context.

8. Create a Drill-Through Destination Page

A drill-through destination is a report page configured to receive selected values from another page. Follow these steps to create a product detail page.

Step 1: Create the Destination Page

  1. Add a new report page.
  2. Rename it to Product Details.
  3. Add a table visual to display product-level information.
  4. Include fields such as Product, Quantity, SalesAmount, and Region where appropriate.

Step 2: Configure the Drill-Through Field

  1. Select the Product Details page.
  2. Locate the Drill-through section in the page's configuration area.
  3. Drag the field that should control navigation, such as ProductCategory, into the drill-through field well.
  4. Confirm that the field is available and correctly related to the detail data.

Step 3: Add Supporting Information

  1. Add a card for the selected category's sales total.
  2. Add a table showing the relevant products.
  3. Format titles and values for readability.
  4. Confirm that the visuals respond to the incoming drill-through filter.

Step 4: Test Navigation

  1. Return to the summary page.
  2. Right-click a category data point in a compatible visual.
  3. Choose Drill through and then Product Details.
  4. Verify that the destination page shows details for the selected category.
Tip: Use the same business field consistently on the source and destination pages. If the destination page expects ProductCategory, test the drill-through action using a source visual that provides a compatible ProductCategory value.

9. Add a Back Button

A back button helps users return from the detail page to the page they were exploring. Power BI supports a back button for drill-through navigation, and report authors can also configure buttons and actions where appropriate.

Back Button Setup

  1. Open the drill-through destination page.
  2. Insert a Back button using the available button options.
  3. Verify that its action is configured for back navigation.
  4. Position the button near the top of the page.
  5. Test the complete drill-through journey from the summary page.

Example Detail Page Layout

← Back Product Details Selected Category Electronics Total Sales $6,900 Product Quantity Sales Amount Laptop 3 $4,500 Monitor 6 $2,400

A well-designed destination page shows the selected context clearly and provides useful detail without overwhelming the user.

10. Preserve or Remove Filters

Drill-through pages can preserve relevant filter context from the source page. Depending on the report configuration, authors can also control whether other filters are retained when users navigate to the detail page.

Configuration Typical Effect
Keep relevant filters Retains additional filter context, such as a selected year or region, where supported.
Remove other filters Allows the destination page to focus on the drill-through field rather than carrying every other source filter.
Destination-page slicers Allow users to further refine detail data after navigation.
Testing requirement: Do not assume that all filters behave identically across every report. Test the destination page with the actual slicers, relationships, and drill-through fields used in your report.

11. Common Problems and Solutions

Problem Possible Reason Suggested Check
Drill-down controls are unavailable The visual has no additional hierarchy level. Add the required fields to the visual's hierarchy.
Months appear in the wrong order Month names are sorted alphabetically. Sort the month name by a month-number column.
Drill-through is not available in the context menu The destination page may not be configured, or the source point may not provide a compatible field value. Review the destination drill-through field and source visual.
Detail page shows unexpected results Filter context or model relationships may not match the intended analysis. Check the drill-through field, relationships, and filter settings.
Back navigation is confusing The destination page does not provide a clear return action. Add and test a suitable Back button.
Some detail rows are missing Additional filters or relationships may restrict the visible rows. Inspect slicers, filters, relationships, and the selected data point.

12. Practical Business Scenario

A sales manager at CIIT TRANING INSTITUTE wants to investigate monthly performance and identify which products contributed to regional sales. Build a report with a summary page and a product detail page.

Required Report Pages

Page 1: Sales Overview

  • Total Sales card.
  • Sales by Year and Month chart.
  • Sales by Product Category chart.
  • Region and Year slicers.

Page 2: Product Details

  • A table containing Product, Quantity, and SalesAmount.
  • A card displaying the selected product category's sales.
  • A drill-through field configured for ProductCategory.
  • A Back button.

Expected Workflow

  1. Select a region and year on Sales Overview.
  2. Drill down from year-level results to month-level results.
  3. Right-click a category data point.
  4. Open the Product Details drill-through page.
  5. Verify the category and other intended filters.
  6. Use the Back button to return to the overview.

13. Hands-On Practice Tasks

Task 1: Create a Date Hierarchy

  1. Use a date table containing Year, Quarter, Month, and Date fields.
  2. Create a column chart using Total Sales and the date hierarchy.
  3. Practice drilling down from Year to Quarter and Month.
  4. Use Drill up to return to the summary level.

Task 2: Create a Product Detail Page

  1. Create a new page called Product Details.
  2. Add a table with Product, Quantity, and SalesAmount.
  3. Configure ProductCategory as a drill-through field.
  4. Return to the summary page and test the context menu.
  5. Confirm that the detail page shows the selected category.

Task 3: Test Filter Context

  1. Select a region and year on the source page.
  2. Navigate to the drill-through page.
  3. Compare the displayed results with your intended filter behavior.
  4. Test the Back button and confirm the source page remains usable.

14. Knowledge Check

Question 1: What does drill down do?

A. Opens Power Query

B. Explores lower levels of a hierarchy in a visual

C. Deletes a report page

D. Changes a column's data type


Question 2: What does drill through do?

A. Opens a separate report page with related filter context

B. Automatically creates a new database

C. Removes all relationships

D. Converts a measure into a column


Question 3: Which example represents drill down?

A. Moving from a Year view to a Month view in a hierarchy

B. Opening a settings dialog

C. Refreshing a data source

D. Renaming a report file


Question 4: What is commonly required on a drill-through destination page?

A. A compatible drill-through field

B. A gradient background

C. A separate Power BI account for each page

D. A new database for every visual


Question 5: Why should you test filter context on a drill-through page?

A. To ensure the detail page shows the intended records

B. To remove every relationship from the model

C. To prevent all slicers from working

D. To turn charts into images

Show Answers
  1. B — Drill down explores lower hierarchy levels.
  2. A — Drill through navigates to another report page with related filter context.
  3. A — Year to Month is an example of hierarchy exploration.
  4. A — The destination page needs a compatible drill-through field configuration.
  5. A — Testing verifies that the intended detail records are displayed.

Summary

Drill down and drill through help users investigate Power BI data at different levels of detail. Although they are related, they serve different purposes.

  • Drill down: Explore lower levels within a hierarchy in a visual.
  • Drill up: Return to a higher level of the hierarchy.
  • Drill through: Navigate to another report page with related filter context.
  • Hierarchies: Organize fields such as Year, Quarter, Month, and Date.
  • Destination pages: Display related details and should provide clear navigation.
  • Filter context: Must be tested to ensure the expected records appear.

Practice both features with a realistic sales dataset before publishing your report. They make it easier for report users to move from high-level business results to meaningful details.