11. Drillthrough Filters

Overview

Drillthrough filters allow you to present a full report page filtered according to a user’s selection on a different page. We’ll learn how to create them in this lesson.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Lesson Goal (00:11)

    The goal of this lesson is to use a drillthrough filter to create a dynamic page of visuals in our report.

  2. Creating a Drillthrough Filter (00:18)

    A drillthrough filter allows us to filter charts on one page of a report by selecting a value from a chart on another page. When we apply a drillthrough filter, all of the charts on the page will be filtered to show data relevant to our selection. 

    To create a drillthrough filter, we start on the page that will be filtered. We drag the field that will be used as a filter to the drillthrough area at the bottom of the visualizations pane to create the filter. When we do this, a back arrow appears on the canvas. This arrow will allow us to return to the page where the filter originates later on.

  3. Using a Drillthrough Filter (01:11)

    To use a drillthrough filter, we simply interact with any chart that contains the relevant field. For example, if we create a drillthrough filter for sales people, then we can access the filter through any chart in the report that uses the sales person field. If we right click a data point for an individual sales person, and select the Drillthrough option, we can go to the report page where the drillthrough filter was created.

    When we reach this page, all the visuals are filtered to show only data for the selected sales person. We can use the back arrow to return to the page where we selected a sales person. The drillthrough page is therefore a report page that is responsive to a user’s selection on another report page.

  4. Keeping Existing Filters (02:34)

    The Drillthrough section of the visualizations pane includes an option to keep all filters. If this option is turned on, then all filters applied to the initial report page are carried over to the drillthrough page. If this option is off, then only the field that we use to drillthrough is carried over to the drillthrough page. Keeping all filters can help you use the drillthrough feature to quickly focus on a specific subsection of your data.

Transcript

In the previous lesson, we looked at visual, page and report filters and how they differ from slicers. In this lesson, we'll use a drillthrough a filter to create a dynamic page of visuals in our report.

To illustrate drillthrough filters we've created two new pages in our report. The drillthrough filters page displays a simple bar chart of revenue by salesperson.

The drillthrough result page contains copies of several charts that we've seen in previous lessons. A drillthrough filter allows report users to filter a chart on one page based on a selection from another page. In this example, we'll select a salesperson from the drillthrough a filters page and see how it effects the visuals on this page. Let's see this in action.

Drillthrough filters are added using the drillthrough area at the bottom of the visualizations pane. We'll drag the salesperson field into this area.

This has not immediately changed the charts on this page, but we can see that a back arrow has appeared in the top left corner of the canvas. The back arrow allows users to return to the page where the filter originates. Let's navigate to the drillthrough filters page.

This chart shows us revenue by salesperson.

To access the drill through filter we need to right click on one of the bars, say that of Holmberg. We then select drillthrough and drillthrough result.

This brings us back to the drillthrough result page, however, the charts are now all filtered to show only data for the sales person Holmberg. We can see that Holmberg generated $0.7 million of revenue in the Midwest region more than any other region. The backer on the top left corner is most useful if the report is uploaded to the Power BI online service where people can view the report without editing it. However, we can try it out here by holding Ctrl and clicking the arrow.

This brings us back to the drillthrough filters page.

Let's try another drillthrough filter. This time we'll right click on the bar for Russ, select drillthrough and drillthrough result.

Again, we're brought to the drill through result page but this time our charts are filtered for the salesperson Russ.

The drill through filter can be used from any visual in this report that features salespeople.

In effect this drillthrough page is a full report page that's dynamic and responsive to the user selection on any other page.

When setting up a drillthrough we can decide whether we want to keep all the filters.

We can see that this switch is currently off. To understand what this means let's return to our drillthrough filters page and add a slicer containing the region field.

We'll select the South region from this slicer.

Let's drill through for the salesperson Deland.

On the drillthrough results page the charts are filtered to show information for Deland only, however, the region filter has not been carried over.

The charts on the right of this page are clearly showing information for all regions. Let's now select the keep all filters option in the drillthrough section.

We'll then return to the drillthrough filters page and drillthrough again for Deland.

This time we can see that the region filter has been carried over and the charts only show information for the South region.

When we use the keep all filters option, any filters or slicers applied to the drillthrough filters page are carried over to the drillthrough results page.

This can be helpful if you want to use the drillthrough feature to focus on a specific subsection of your data. Let's stop the lesson here.

In the next lesson we'll learn how to use what if parameters in our visualizations.

Dashboards and Visualizations
Introduction to Visualizations in Power BI