Power BI Snack : Drillthrough in Power BI

Case
In SQL Server Reporting Services (SSRS) we are used to use subreports and drillthrough, but in Power BI this was not possible until the release in September. How does it work?

Drillthrough in Power BI

Solution
As said, we use this functionality a lot in SSRS. For example, when you are dealing with multiple audiences who use the report. Managing board or senior management are interested in the total sales per year/month. An operational manager is probably interested in the same numbers, but then by week/day, per location and more details about the products sold.

To build a drilltrough report, we created a Power BI report based on the sample database of Microsoft. You can download the database WideWorldImporters and more here. Our first report is the main report, where we are showing the total sales and profit per month and per state. You can also filter this data per year.

1) Configure the drillthrough filter
After creating the main report, we want to show more details about the sales and profit per state. For this we have built another report (subreport) with a number of graphs and one table for the details. This table shows the sales and profit per city. Next, we add the Drillthrough filter in the subreport. Go to Filters and now you also see Drillthrough filters as possibility. Add the appropriate column here, in this case 'State Province'.

Power BI - Drillthrough filter

Once you added the filter, an arrow (icon) appears automatically in the upper left corner of the "subreport". By clicking this arrow, you return to the main report. You can customize this arrow to your desired layout.

Power BI - Back to main report navigator (default and customized)

2) Result

Now we can use the drillthrough functionality in the main report. Go to the table and right click on a state, for example 'Alabama'. At the bottom, select Drillthrough and the subreport Sales per State. The subreport will now be opened with all sales data for the state Alabama. When you want to return to the main report, click the blue arrow in the upper left corner.

Unfortunately you can only filter the subreport with the drillthrough filter. So when you have multiple report filters in your report, for example 'Calender Year', the subreport will not filter on year. As soon this is the possibility, the other sub rapport called 'Sales per State (future)' is a better solution.

You can download the Power BI report here.

Power BI - Drillthrough to your subreport

Conclusion
Power BI has taken a good step for more interaction between different reports, hopefully this is just a beginning. There are still some improvements to be made. For example, the possibility to pass through all selected report filters in the main report to the subreport. Now only the selected drillthrough filter is passed by.

You can vote on this idea here. Of course, we already voted.