Last updated on July 28th, 2022 at 01:49 pm
Power BI slicers can help you filter results by only showing a subset of the data. But did you know that Power BI has another method to filter data? This method is through the Power BI drillthrough filter. Power BI drillthrough filters are used to pass values from one report page to another as the selected values are used to filter the results on a second report page. Essentially, drillthrough filters function the same as a page level filter. In this blog post, I talk about how to use Power BI drillthrough filters. More importantly, how to reset them to either display all data or to select a new item to filter.
Keep in mind that I could write a book explaining the differences between slicers and drillthrough filters. But that’s not the point of this blog post. Instead, more posts from me and Leonard @SuaresLeonard about slicers and drillthrough filter options. Like Cross-report and Keep all filters will appear over the next month or so. So stay tuned.
What is a Power BI Drillthrough Filter?
A Power BI drillthrough filter is used to identify the columns that are to be used to drillthrough from one report page to another report page. Once the columns are selected, using the drillthrough command, the second report page only shows the selected (filtered) data.
On the report page, in the DRILLTHROUGH section of the VISUALIZATIONS pane, all of the applied drillthrough filters are listed. In this case there are three applied filters: Installed is Required, UpdateClassification is Critical Updates and Collection Name is Oxford Regional Office. These filters are all used to refine the data displayed on the report page. In a future blog post. I explain why the Collection Name filter is in italics and what that means. But for the purposes of this blog post it is not relevant.
In the animated GIF (see above), you see the process of drilling-down from one report page to another report page. At the tail-end of the GIF you can see where the drillthrough filters are listed.
Changing a Power BI Drillthrough Filter
One of the great features with Power BI is the ability to drillthrough from one report page to another. What do you do, however, if you want to view other column values without having to return to the main report page again? All you need to do is change (or reset, if you like) the filters that are applied to the report page. And yes, you can even add new filters too!
Using the UpdateClassification example above, the filter is reset to show (All) results, instead of a specific update. Simply by clicking on the erase button (yellow arrow). The erase button is displayed when you hover over the drillthrough node for UpdateClassification. What if you want to change the value to another classification and you don’t want to see all results? Click on the down arrow (see the red arrow).
Clicking on the down arrow reveals all of the values available to you. Go ahead and select one of the values!
When you select a value, the report is automatically updated.
Problem When Power BI Drillthrough and Slicer Filters Are Applied on the Same Page
An interesting problem occurs when a slicer filter and a drillthrough filter are applied to a report page at the same time. I will demonstrate this problem by choosing a different collection in the drillthrough filter from the one already selected in the slicer filter.
In the top right-hand side of the report, the Collection Name slicer lists All Desktop and Server Clients (purple arrow). However in the bottom right-hand corner, the Oxford Regional Office is the applied (red arrow) drillthrough filter. I didn’t change the slicer filter. But I did follow my earlier steps as outlined in the section about how to change drillthrough filters. When the collection name is changed to the Oxford Regional Office, the report page becomes blank.
What happened? The two filters cancelled each other out and you end up seeing a report with no results. The drillthrough filter is removing everything. But the data from the Oxford Regional Office collection, and the other filter (the slicer) is removing all data. Again but the information from the All Desktop and Server Clients collection. Since the two filters are mutually exclusive, it results in a blank report page.
To solve this issue, select the slicer and change it to the Oxford Regional Office.
With that one change, the report page displays the correct details.
Summary
You might ask, why would you have a slicer and a drillthrough filter for the same column within the same report? In my case, there are two answers to this question. One answer is convenience. I wanted a slicer to show computer names on the details page in order to make it easier to select a computer. But I also wanted the option to drillthrough from a list report page too. This is where you can get into conflicts with both. The second answer is that I created these filters for demonstration purposes only. Originally, I used a slicer and a drillthrough filter for computer names in my report set. But in the end, I didn’t find the results useful, so I removed the slicer.
If you have any questions about Power BI drillthrough filters or slicers, please feel free to contact me at @GarthMJ.