Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Power BI Drill Through Example


By:   |   Updated: 2019-02-08   |   Comments (3)   |   Related: More > Power BI

Problem

Does Power BI include any drill through navigation features? Can filters be applied to the drill through process?

Solution

Any time you are dealing with data, you inevitably will get the question: "What makes up that number?" Sure, you could write a query to get those details. Or you could ask the user to research the numbers on their own. However, the better answer is to provide a way for users to see the detail within your report or dashboard. One option in Power BI is to use the drill down capabilities of a matrix which works much like a pivot table in Excel. Drill down capabilities work within the same visual using the same dataset. Basically, you are looking at a more detailed level of aggregation all within that one visual. I would recommend taking a look at Siddharth Mehta’s tip on drill down and data analysis at: https://www.mssqltips.com/sqlservertip/5249/data-exploration-drill-down-and-analysis-using-decomposition-tree-in-power-bi-desktop/ . To the contrary, the drill through functionality pertains to a related but somewhat different concept. Drill through reports provide a path for moving from one tab in a report to a completely different tab within that report. You will be looking at a completely different visual to see a detail level of data.

Before you complete any part of the below examples, you need to be sure to download the latest version of Power BI desktop from: https://powerbi.microsoft.com/en-us/get-started/. The download and install process has changed, in that it now uses the Microsoft Store to complete the install. For those wanting the more traditional download and install method, you can still use this site: https://powerbi.microsoft.com/en-us/desktop/.

Power BI Drill Through Process

The first step in the drill through process is to plan your master report and your target report. As you decide on these report tabs, you can actually have multiple drill through reports for a report consumer to navigate to. Thus, a master report could have three "sub" reports that can be opened from the main report. Also, another item to keep in mind is that if you are using Power BI Report Server, some of this functionality may not work. Remember Microsoft pushes out updates for Power BI Desktop, Power BI Service (online), and Power BI Report Server just about every month or so.

For our example, we will be using the AdventureWorks2016 database as our data source ( take a look at these tips https://www.mssqltips.com/sqlservertip/4016/querying-sql-server-data-with-power-bi-desktop/ or https://www.mssqltips.com/sqlservertip/4170/connect-to-onpremises-data-sources-with-power-bi-personal-gateway/ as some examples for connecting to various data sources).

As shown in the below screen print, we add the Production schema tables from the AdventureWorks database to our data model in the Power Query Editor. For this example, no particular transformations other than a few columns renames (to add spaces between words) were completed on the data.

New data set of production data.

Next, we would select close and apply, in order to send the query data sets back to the main report design grid.

Close the query editor and apply to the Power BI Model.

We are now ready to design our Main report tab. This report tab will be where we will drill through from. As illustrated below, the master contains a clustered bar chart and a few slicer visuals. The bar chart uses the actual start date year for the category along with the location for the legend. For the measure value, the actual cost is used from the Production.WorkOrderRouting table. The location name comes from the Production.Locaton table. The actual start date year and location will become the basis for our drill through action.

Design New Master Page for Drillthrough

After setting up the main master page, the ensuing step is to create our detail report tab; this page will be the tab where the user will go to when drilling through. In the below example, a simple table has been created. It contains the location, category, productid, product name, product number, work order ID, actual start date, actual end date, scheduled end date, actual cost, planned cost, and actual resource hours.

Detail Page

Detail Page for Report

We are now ready to add our drill through filters. For this example, drill through by actual start date year and location will be allowed; these fields are the two main component on bar chart visual on the main report tab. In order to complete this task, we first need to be on the detail page. Then, we will drag the Location name field and the actual start date year to the drill through section. Just above the area where the two fields were placed is an option that says Keep All Filters. Turning this option On allows filters used on the main or master page to be passed to the detail page which should allow for the synchronization of the master page filters with the values shown on the details page. Turning this filter off means only the specific fields placed in the drill through field list will be used as filters for the detail page.

Drill through filters added

Once the filters are set, you will notice an arrow image is added to the detail tab. This arrow works as a back button to allow users to quickly navigate back to the original master page.

Back button arrow.

To make it stand out, I adjusted the color of the arrow and also added a title to the arrow image. Furthermore, a text box was added as a title for the detail report. I also added a duplicate tab of the detail report to allow for a second drill through detail page to appear in the drill through action (more on this item soon).

Navigation Arrow

The drill through functionality can now be shown by first heading back to the master tab and right clicking on one of the bars within the bar chart visual. You will notice we have two drill through options. The original detail report and the second duplicate version that was just created.

Drill Through Option

Upon clicking on the Detail Page option (notice how it shows Detail Page and Detail Page 2, the second is the duplicate tab that was created), we are directed to detail page and the visual is filtered by the values for the bar that was selected: 2013 and Frame Welding. You may also be wondering how I got the 2013 and Frame Welding values to show on the detail report. That item is a little trick that makes use of the Card visual. Since only one value can be selected for each of the added drill through filters (start date year and location in this example… more details on multiple values soon), we actually can drop a card visual onto the design grid and then add the corresponding drill through field to the card; thus we add one card for location name and one card for start date year. We are using the card visuals to create a title for the detail report. This setup allows for the report consumers to easily see what bar was selected on the master page.

One other item that should be noted about the navigation arrow that was automatically added. In Power BI Desktop, the Back arrow will only work if the ctrl key is held down while clicking the arrow. Within Power BI Service (online), the arrow only needs to be clicked.

Power BI Navigation Arrow

Heading back to the main page, let us filter the results using the product lines and categories slicers. As you can see in the below example, R & T are the selected product lines and Bikes and Components are selected for the categories.

Main Page Slicers - Using slicers to filter results.

Now when we drill down from the master page, the table results are not only filtered by the year and location, but also by product line and category. This effect is caused by having the Keep all filters set to On.

Drill through filters

If this option is set to off, then only the year and location (the named drill through filters) are used, as shown below.

Slicers not used on detail page.

You may also be wondering how the product line and category are being show on the detail page similar to the year and location cards. The same concept is used whereby a card visual is utilized yet is expanded. Since these filters allow for multiple values to be selected, we instead use a multi-row card visual to display the values that are selected within the slicers.

As we wrap up our tip on drill through functionality, an additional enhancement that was recently added to the drill through functionality was the ability to add not only regular dimensional type fields, but also allows measures to be added as one of the drill through filters. As illustrates subsequently, adding the actual cost as one of the drill through measures allows that item to be included in the filtering process. Each of these drill through filters can be used as a category (most like especially for non-numeric field values) or summarized.

Drill Through Measure

As you can see the drill though functionality adds a greater layer of interactivity to a Power BI report with multiple tabs; this functionality is a perfect complement to the summary visuals and KPI reports that are found on most high-level dashboards.

Next Steps


Last Updated: 2019-02-08


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, February 08, 2019 - 8:06:05 AM - Scott Murray Back To Top

You will need to reveiw all your data in PowerBI Desktop.


Friday, February 08, 2019 - 4:41:15 AM - Sharjeel Riaz Back To Top

Hi, I am creating the following measure to filter the products  which are out of stock :

FLAG_STOCK = IF([QTY] <=0 && [Avg.Sold Per Week ] >0 && [Weeks Cover]=0 ,T,F) 

Thats working fine on power bi desktop but when publish my report on power bi service it gives the following error on all of the visuals

"Couldn't load the data for this visual

The query referenced calculated column 'prod_stock'[FLAG_STOCK] which does not hold any data because evaluation of one of the rows caused an error.

Please try again later or contact support. If you contact support, please provide these details."


Friday, February 08, 2019 - 4:33:58 AM - Sharjeel Riaz Back To Top

Hi, can we implement drill through on single click?


Learn more about SQL Server tools