In Business Intelligence & Analytics
In this post we explain how this feature can be easily implemented in Power BI and what to consider when implementing such a scenario.
Management
One of the most common requirements in any reporting solution is the ability to navigate from the home page to the details page and view item or category details. The drill-through filter is not a new feature in Power BI, but it's one of the most useful and desirable features that greatly simplifies your life for report development and visualization.
With drill-through in Power BI reports, it is possible to create a landing page in the report that focuses on a specific entity, such as a supplier, customer or manufacturer. When report readers right-click on a data point in other pages of the source report, they drill-through to the target page to get the filtered details in that context. You can configure drill-through in reports in Power BI Desktop or in the Power BI service.
Example scenario
Here we have a sample report that shows sales volume by education category and gender. The data in this report comes from AdventureWorksDW.
As you can see in the above report, there is no room to add more visual tools. We want to create the ability to navigate to one of the education categories and see the details of that category. We want to see some details such as customer name, order volume and other information along with sales volume. Let's see how this is possible.
The detail filter
A page that has end-to-end functionality can be any page in Power BI Desktop. You just need to create a page and put all the visual tools on it Here is an example of a page I created:
One of the important considerations for the information page is the name of that page. This is the name you will see in the detail option from the home page. This should be something that lets the user know it is a details page.
To convert this page to a detail page, you need to drag the data field you want to transfer from the home page to the details page into the Drillthrough filter section of the details page. On the home page, we want to go to the details of the EnglishEducation field. So, this is the field you need to drag and drop into the Drillthrough section.
After dragging the field, you will see a filter-like section where you can select different values. Do not select anything in this section. You will also notice that a BACK button has been added to the top left-hand side of the page.
This button will allow you to return to the page you came from (which in this case will be the home page). You can format the button if you like, change the colours, add text and other formatting options. You can apply any formatting you like. The only part you have to save is the action of this Back button type. The back action ensures that the user can return to the home page from this page with detailed information.
Functionality testing
Now let's see how this detail functionality works. Go to the home page and right click on the column in the chart that has education as part of its axis, then you will see the Drillthrough option that will take you to the details page.
Once you have clicked on Education Category Details you will be taken to the details page and the details page will only filter for the category you came from (in the screenshot above, Partial College)
Everything on this page is filtered for the education category we detailed on the main page. You can now go back to the previous (main) page using Ctrl + click.
Note that button actions require Ctrl+Click in Power BI Desktop. They work with a normal click on the website.
Some improvements
The detail experience can be improved by adding the selected item to the header of the report page. For this purpose you can easily use the map visualisation and show the EnglishEducation there. When you add a text box to a visual map, it shows the first value of that item in the table. When only one item is selected, it is the first item. As a result, you always get the selected category using this method.
You can also remove the category label, as the First EnglishEducation heading is misleading.
You can now see the selected education category on the details page for any selected category.
Retention of all filters
One of the great features added to this functionality is the "Keep all filters" option. This option is enabled by default. And it will pass all filters from the home page to the details page. Let's see what this feature means. Suppose you also want to go to the details of the education and gender categories. For example, you want to detail Highschool data for Male. If you go to the home page and click on the map that has both filters, use the Drillthrough option, you will be able to do this without any further action.
As a result you can now see the details page showing only EnglishEducation as High School and Gender as Male.
The Keep all filters option means that it will pass any filter from the home page to the details page, even if you have not added this field to the Drillthrough section of the details page. If you disable this option, Drillthrough will only work for specified fields.
Multiple choice
You can use the same approach to create a visual map for the selected Gender option on the details page.
The problem you may encounter now is that if someone uses the Drillthrough option on the main page, on a chart where there is no Gender option.
The details page in this case will show both genders, as you can see in the table. But the visual map we used as part of the header just shows the former, which is wrong!
The reason for this behaviour is obvious; you have more than one value and the visual map only selects the first instance of the value. What is the solution then? You have two options: one is to use a visual tool that shows multiple values. Such as Multi-Row, visual maps or tables, 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 which values are selected. You can create a measurement with the following expression:
Selected Gender = SELECTEDVALUE(DimCustomer[Gender], "All")
The expression above tells you this; if the selection is by gender, the dimension will return it. Otherwise it will return the text: "All". This dimension can now be used in the visual map. Here is the result:
Multiple detail pages
You can have multiple detail pages, here is an example of how they would look from the user's point of view;
Summary
Drillthrough is one of the most common things you can do to save space on the home page and add more detail to the details page. The Drillthrough filter in Power BI works with minimal configuration for this behaviour. In this post you have seen how you can implement a script that the user can move from the home page to the detail page and back again.