What is drill through filtering in Power BI?
Drill through filtering is a feature in Power BI that allows users to explore and navigate through their data between charts. It enables users to start with an overview of their data and then progressively narrow their focus to uncover more detailed information. This interactive functionality makes it easier to spot trends, patterns, and outliers within more complex datasets.
When is drill through filtering used?
Drill through filtering is particularly useful when dealing with data that has multiple levels of granularity or hierarchical structures. For example, in financial reporting, you can start with an annual overview of revenues, and then drill down into quarterly, monthly, and daily figures. In geographical data, you can begin by looking at a global map and then drill down into regions, countries, and cities. This feature is beneficial for users who want to explore data progressively, gaining a better understanding of trends and anomalies at different levels of detail, all within a single interactive dashboard.
How to step up drill through filtering?
There are two steps to this.
1) To apply a filter between pages, you need to begin with the target page (the sheet you would like to apply the filters too). From here you want to drag your filter field from the data pane into the visualization pane, and drop it into “Add drill through filter here”. This now ensures that when this field is selected on other pages, the filter will be applied to the target page.
2) Next, you can create a button from a shape of your choice in your source sheet. You can find this under the Insert Ribbon. In this example, we have selected a chevron.
Click on the button and under the Format Tab, Click Action, Click Drill through and select your target sheet as the destination, for me this is "Cost Breakdown".
You can also format your button and add text under the Style tab. Now you should be able to click your filter. To do so, select drill through (CTRL+Click) and this will take you from your source sheet, to your target sheet, with the filter applied.
When setting up your drill through, a button will automatically be created in your target page. This button will allow you to remove your filter and return to the orginal view.
Now you have created your first drill through in Power BI.