One of the most common requirements in any reporting solution is the ability to drill through from master page to the details page and see the details of an item or category. In this post, I will explain how this feature is simply possible in Power BI, and what are things to consider when you are implementing such a scenario. The drill-through filter is not a new feature in Power BI, but is it one of the most helpful and wanted features which makes your life much easier for designing reports and visualizations. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Sample Scenario

Here I have a sample report which demonstrates sales amount by education category and gender. The data in this report is coming from AdventureWorksDW.

As you can see in the above report, there is no space to add more visuals. I want to create the ability to drill down into one of the education categories and see the details of that category. I want to see some more details, such as the name of customers, the quantity of the order, and other information alongside with sales amount. Let ‘s see how this functionality is possible.

Drillthrough Filter

A page that has the drill-through functionality can be any page in Power BI Desktop. You just need to create a page and put all visuals you need on that page. Here is a sample page I have created:

One of the important considerations for the details page is the name of this page. This name is what you will see in the drill through option from the master page. It should be something that gives the user the understanding that this is a details page.

To convert this page to a drill-through page, you need to drag and drop the data field that you want to pass from the master page to the details page, into the Drillthrough filter section of the details page. From the master page, we want to go to the details of EnglishEducation field. So that is the field you need to drag and drop to the Drillthrough section.

After dragging and dropping the field, you will see a section like a filter that you can choose different values of the EnglishEduction. Do not select anything in that section. You will also notice that there is a BACK button added to the top left-hand side of your page.

This button will enable you to navigate back to the page that you are coming from (which in this case, would be the master page). You can format the button if you like, changing colors, adding texts, and other formatting options are possible. You can apply whatever formatting you like. The only part you should keep intact is the Action of this button to be of type Back. The back action makes sure that user can get back to the master page from this details page.

Testing Functionality

Now let’s see how this drill-through functionality works. Go to the master page, and right-click on a column in the column chart that has education as part of its axis, then you will see a Drillthrough option that will lead you to the details page.

Once you click on the Education Category Details, you will be navigated to the details page, and the details page will be only filtered for the category that you came from (in the screenshot above, Partial College)

Everything in this page is FILTERED for education category that we drilled from the master page. You can now go back to the previous (master) page with Ctrl+click.

*Note that button actions need Ctrl+Click in the Power BI Desktop. They work with a normal click on the website.

Some Improvements

Drill-through experience can be improved with bringing the selected item somewhere in the header of the report page. For this purpose, you can easily use a Card Visual and show EnglishEducation there. When you add a text field to card visual, it will show the First value of that item in the table. When only one item is selected, then the first item is that item itself. As a result, you always get the selected category through this method.

You can also remove the category label, as the title of First EnglishEducation is misleading.

Now you can see the selected education category in the details page for any category selected.

Keep All Filters

One of the great features added to this functionality is “Keep all filters” option. This option by default is on. And it will pass all filters from the master page to the details page. Let’s see what this feature mean. Let’s assume you also want to go to the details of an education category and a gender. For example, you want to drill-through to Highschool data for Male. If you go to the master page and click on a chart that has both filters on it, then use Drillthrough option, you get the option to do it without any extra actions.

As a result, now you can see the drill-through details page showing only EnglishEducation as High School, and Gender as Male.

“Keep all filters” option means it will pass any filter from the master page to the details page, even if you have not added that field in the Drillthrough section of the details page. If you turn this feature off, then Drillthrough only works for pre-defined fields.

Multiple Selection

You can use the same approach to create a card visual for selected Gender in the details page.

The challenge that you may face now is what if in the master page, someone uses the Drillthrough option, on a chart that doesn’t have Gender on it?

The details page, in that case, will show both genders as you can see in the table. But the card visual that we used as part of a header is just showing the first, which is wrong!

The reason for this behavior is obvious; you have more than one value, and the card visual picks only the first value instance. What is the solution then? You have two options: one is to use a visual that shows multiple values. Such as Multi-Row card visual, or table, and set the field as Do Not Summarize.

Or another approach is to get help from DAX. You can write a simple DAX expression to find out what are the selected values. You can create a measure with expression below:

Selected Gender = SELECTEDVALUE(DimCustomer[Gender],"All")

The expression above is saying that; if gender is selection, the measure will return that gender. As a result, otherwise, it will return a text: “All.” This measure can now be used in a card visual. Here is the output now:

Multiple Details Pages

You can have multiple details pages, here is an example of what it will look like from a user point of view;

Summary

Drill-through is one of the most common reporting actions that enable you to save some space on the master page, and add more details in a details page. The Drillthrough filter in Power BI works seamlessly with minimum configuration for this behavior. In this post, you have seen how you can implement a scenario that the user can navigate from master page to the details and back.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.