| Concept | Description |
|---|---|
| Drill Patterns | |
| Drill-Down | Hierarchy navigation that takes the user from summary to detail |
| Drill-Up | Returning back up the hierarchy after drilling down |
| Drill-Through | Right-click navigation to a detail page filtered to the selection |
| Cross-Filter and Stories | |
| Cross-Highlighting | Selecting a visual highlights matching slices in others |
| Cross-Filtering | Selecting a visual filters the rest of the page |
| Edit Interactions | Choosing how each visual responds to selections elsewhere |
| See Records and Data | Inspecting the rows behind a visual selection |
| Bookmark Storytelling | Sequenced bookmarks turn a report into a guided walk-through |
15 Drilldowns & Cross-Filtering
NoteExploring Data at Multiple Levels of Depth
One of the most powerful aspects of Power BI is that a report does not have to show everything at once. Data naturally exists at multiple levels of granularity: a year contains quarters, quarters contain months, months contain days. A country contains regions, regions contain states, states contain cities. A product category contains sub-categories, sub-categories contain individual products.
Drilldown lets consumers navigate through these levels of granularity interactively, moving from a high-level summary down to the specific detail they care about, and back up again. Cross-filtering lets every visual on a page communicate with every other visual, so that clicking one chart automatically updates all the others to reflect the selection. Together, these two capabilities transform a static collection of charts into a dynamic, interconnected analytical experience.
15.1 Working with Hierarchies
15.1.1 Hierarchies in the Data Model
NoteWhat Is a Hierarchy?
A hierarchy is a named, ordered set of columns that define levels of granularity from broadest to most specific. When a hierarchy is placed on a chart axis or in a matrix row, Power BI displays the data at the top level initially and provides controls to navigate down through the levels one at a time. Without a defined hierarchy, drilldown still works through date hierarchies (which Power BI creates automatically for date columns) but requires the levels to be present in the field wells.
Hierarchies make it easier for report builders to place multiple related levels on a visual with a single drag, and they make it clearer to consumers that the levels are related and navigable.
NoteHow to Create a Hierarchy
To create a hierarchy in the data model:
- Switch to the Model view or open the Data pane in Report view
- Right-click the top-level field you want as the first level of the hierarchy (for example, Country in DimLocation)
- Select Create hierarchy. A new hierarchy object appears beneath the table named “Country Hierarchy” by default
- Rename the hierarchy by double-clicking its name and typing a descriptive label such as “Geography Hierarchy” or “Location”
- Drag additional fields into the hierarchy in the correct order from broadest to most specific: Country → State → City
- The hierarchy now appears as a single expandable item in the Data pane and can be dragged onto a visual axis or field well as a unit
[Insert screenshot of the Data pane showing a DimLocation table with a “Geography Hierarchy” object beneath it, expanded to show Country, State, and City as its three levels listed in order, each with a hierarchy level icon]
NoteDate Hierarchies
Power BI automatically creates a date hierarchy for any column with a Date or DateTime data type. The auto-generated hierarchy contains Year, Quarter, Month, and Day levels. When you drag a date column onto a chart axis, Power BI places the entire date hierarchy there automatically, enabling drilldown from year down to day without any manual hierarchy setup.
You can see and customize the auto-generated date hierarchy in the Data pane by expanding the date column’s dropdown arrow. If your Date dimension table has custom fiscal calendar columns (Fiscal Year, Fiscal Quarter, etc.), create a manual hierarchy from those columns to make fiscal-period drilldown available as well.
[Insert screenshot of the Data pane showing a date column expanded to reveal the auto-generated hierarchy with Year, Quarter, Month, and Day listed as sub-levels beneath the column name]
TipAlways Create Explicit Hierarchies for Commonly Drilled Dimensions
While drilldown works without a formal hierarchy by stacking fields manually in a visual’s field well, creating explicit hierarchies in the model makes the report-building experience significantly faster and more consistent. Any report builder working with the model can drag the hierarchy onto a visual and immediately get the correct drill levels in the correct order, without needing to know which fields to stack or what order they belong in.
15.3 Cross-Filtering and Highlighting
15.3.1 Cross-Filtering and Cross-Highlighting
NoteWhat Is Cross-Filtering?
Cross-filtering is the behavior where clicking a data point in one visual automatically filters all other visuals on the same report page to show only data relevant to that selection. When a consumer clicks the “Electronics” bar in a product category chart, every other visual on the page, including the regional map, the trend line chart, and the customer table, immediately updates to show only Electronics data.
This interconnected behavior is one of the defining characteristics of interactive Power BI reports and is what makes a report feel like a live analytical tool rather than a static document. Cross-filtering happens automatically between most visual types without any configuration required.
[Insert screenshot of a report page in two states: on the left with no selection and all visuals showing full data, and on the right after clicking “Electronics” in a category bar chart, with all other visuals on the page now filtered to show only Electronics data, highlighted selections visible and non-selected portions greyed out or absent]
NoteCross-Filtering vs. Cross-Highlighting
Power BI distinguishes between two behaviors when a consumer clicks a data point:
Cross-highlighting is the default behavior for most visual combinations. When you click a data point, other visuals dim (grey out) the portions of their data that do not match the selection, while keeping the full bar or line visible. The highlighted portion represents the matching data and the greyed portion represents the non-matching context. Both are visible simultaneously, allowing the consumer to see both the selection and the total in the same visual.
Cross-filtering removes the non-matching data entirely from the other visuals rather than dimming it. Only the data matching the selection remains visible. Cross-filtering gives a cleaner, more decisive result but removes the contextual reference of the full total.
The default behavior (highlighting vs. filtering) between specific pairs of visual types is controlled in File → Options and Settings → Options → Report settings → Default visual interaction.
[Insert screenshot showing the same report page with the same selection applied twice: the top row showing cross-highlighting with full bars partially greyed out, and the bottom row showing cross-filtering with only the matching data remaining and non-matching bars completely absent]
15.4 Controlling Visual Interactions
15.4.1 Edit Interactions
NoteWhy Edit Interactions?
The default behavior where every visual responds to every other visual on the page is appropriate for general exploration but can be problematic in specific cases. A grand total Card visual should not filter down when a category bar is clicked. A reference benchmark line chart should stay constant regardless of slicer selections. A data quality summary table should always show the full dataset.
Edit interactions gives the report builder precise control over exactly which visuals respond to which other visuals, and how they respond (filter, highlight, or not at all).
NoteHow to Edit Interactions
- Select the source visual whose click behavior you want to configure (the visual that the consumer will click on)
- Go to the Format ribbon and click Edit interactions. Alternatively, go to the visual’s three-dot More options menu and select Edit interactions
- Small interaction control icons appear in the top-right corner of every other visual on the page
- For each target visual, click the icon that represents the desired behavior:
- Filter icon (funnel shape) — the target visual shows only data matching the selection in the source visual
- Highlight icon (overlapping bars) — the target visual dims non-matching data while keeping full totals visible
- None icon (circle with a line) — the target visual does not respond to selections in the source visual at all
- Click Edit interactions again in the Format ribbon to exit editing mode and return to normal report view
[Insert screenshot of a report page in Edit interactions mode, showing the three small interaction control icons (Filter, Highlight, None) in the top-right corner of each non-source visual, with one visual showing the None icon selected indicating it will not respond to clicks on the source visual]
NoteCommon Interaction Configurations
The table below shows the most frequently used interaction configurations and the reasoning behind each.
| Source Visual | Target Visual | Recommended Interaction | Reason |
|---|---|---|---|
| Category slicer | All data visuals | Filter | Clean filter behavior expected |
| Bar chart (category) | Line chart (trend) | Filter | Trend should show only selected category |
| Bar chart (category) | Grand total Card | None | Card should always show the full total |
| Region map | KPI Cards (fixed benchmarks) | None | Benchmarks should not change with regional selection |
| Matrix (detail) | Summary chart | Highlight | Summary context should remain visible |
| Date slicer | All visuals | Filter | Date selections should apply universally |
[Insert screenshot of a report page showing a well-configured interaction map: a category bar chart with Filter icons on the trend chart and data table, and None icons on the two KPI card visuals in the header area, demonstrating intentional interaction design]
TipSet Interactions Before Sharing the Report
Interaction configurations are invisible to report consumers. They cannot see whether a visual is set to Filter, Highlight, or None. They can only observe the behavior. Always test every combination of clicks across your report before publishing to ensure the behavior is intuitive and consistent. A consumer who clicks a bar and notices that one visual does not respond may not understand why, and may lose trust in the report if they expect all visuals to be connected.
15.5 Bringing It All Together
15.5.1 Bringing Drilldown and Cross-Filtering Together
NoteA Combined Interaction Example
Drilldown and cross-filtering are most powerful when used together in the same report. Consider a sales report with the following layout on one page:
- A column chart showing Revenue by Year (with a Year > Quarter > Month hierarchy on the axis)
- A filled map showing Revenue by Region
- A table showing top customers by revenue
- Two KPI cards showing Grand Total Revenue and Grand Total Orders
When the consumer clicks the 2024 bar in the column chart with drilldown mode on, the chart drills to show the four quarters of 2024. Simultaneously, because cross-filtering is active between the column chart and the map and customer table, the map shades only the regions with 2024 revenue and the customer table shows only the customers who purchased in 2024. The two KPI cards, set to None interaction, remain unchanged showing the all-time totals for context.
The consumer then clicks Q3 in the drilled chart, drilling further to the three months of Q3 2024. The map and table filter again to the Q3 2024 context. When finished, clicking Drill up twice returns the chart to the Year level and the other visuals restore to their full-data state.
This combination of drill navigation and cross-filter propagation allows a single page of four visuals to serve as both a summary view and a deep-detail exploration tool simultaneously.
[Insert screenshot showing the report page described above in its drilled-down state (at the Quarter level for 2024), with the map and customer table both filtered to the 2024 context, and the KPI cards unchanged showing all-time totals]
ImportantDrilldown and Cross-Filtering Are About the Consumer’s Journey
Every drilldown path and every cross-filter interaction is a step in the consumer’s analytical journey. Design these interactions with the consumer’s questions in mind: what will they want to see when they click this bar, what level of detail is genuinely useful versus overwhelming, and which visuals should anchor the page as fixed references while others respond dynamically. A report that anticipates and guides the consumer’s exploration journey is one that gets used, returned to, and trusted.