Drill Down and Up in Power BI Explained

Power BI drill down and up explained

Power BI works with hierarchies very easily, and there are buttons to drill down and up. However, sometimes, these buttons are confusing for the users. It is essential to know what each drill down/up option is and how it works, not just for developers but also for the end-users. In this blog and video, I’ll explain that.

Video

Having a hierarchy in a Power BI Visual

You will see the drill-down/and up options on a visual if you have a hierarchy of fields in that visual. A hierarchy in a visual can be simply created by dragging and dropping multiple fields one after the other into the same field’s section of a visual. This can be also done by having a date field which is using the built-in Power BI date hierarchy and table.

For example, in the visual below I have SalesAmount as the Value of the column chart, and the Date field (OrderDate) as the X-Axis.

Date hierarchy in a Power BI visual

This will lead to seeing drill down/up buttons on the top of the visual (or at the bottom of it if the visual is touched at the very top of the report)

Drill down/up buttons in Power BI visuals

Let’s see how these options work

Selective drill down: Data Exploration

To understand the very first ability in the drill-down options, let’s see what is the default action when we click on a column in the column chart; The default action is that the column gets highlighted;

highlighting a value in a Power BI visual

Now, let’s see what the selective drill down is. Click on the single arrow down, which is also called drill mode. and turn it on.

Turn drill mode ON

This means now that visual is in drill mode. If you click on a column, it will drill down to that section of the data, for example, If I click on 2007, then it will drill down to the quarters of that year.

If you click on a quarter, then you will see the visual drills down to the months of that quarter;

This action will traverse the levels of the hierarchy until the lowest level for as long as you click on a column. You are selectively drilling down to specific data sections.

If we use the Drill Up button (the single arrow up) then we go one level up in the hierarchy;

drilling up in a visual in Power BI

This ability to selectively drill down and drill up to areas you want is a fantastic data exploration technique. This can be used to find anomalies in the data and going and investigating that area more in detail.

The selective drill-down is possible as long as you have the drill mode as ON. Make sure to turn it OFF when you have finished the data exploration.

There is a small problem in the selective drill down, and that is; if you are in a lower level, you don’t know which higher level you are in. for example, when you are in days, you don’t know that what month these days belong too. There is a simple workaround to get this fixed, which I explained that in my other article here;

All-in-one drill down: Seeing the trend

Another way to drill down is to drill down to the second level of the hierarchy, but not just for the selected year. You may want to see throughout all years how the sales changes, but you want to see that in quarter level. Meaning you want to see all years and their quarters in the X-Axis. The selective drill down doesn’t give you this option. To achieve this, you can use the fork-like button.

expand all down one level in the hierarchy in Power BI

If you are practising with the sample file, ensure to first drill all the way up to the year level and turn off the drill mode from the previous practice. Then you can click on the Expand all down one level in the hierarchy. this immediately changes your X-Axis to include both year and quarter for all year.

another expansion all down one level will bring also month to the X-Axis besides the year and quarter

This is an amazing way to see the trend. As you can see, we can see the trend through the years and months all in one view. This is not selective data exploration, instead, it is a good way to see the trend of changes in the value.

You can use the same drill up button to go up.

Next-level Branching; regardless of the higher level

The third method of drill-down is more like a branching than a drill down. Drill up again to the highest level before practising this sample. Let’s say you are interested to see which quarter is best-seller and which quarter is worst. You don’t care about the year, you want to do this analysis for all years but at the quarter level. You can of course achieve this by creating a visual which has SalesAmount as the Value, and Quarter as the X-Axis. However, you can do the same using a drill-down option here called; Go to the next level in the hierarchy. This is a two-arrow-down button.

go to the next level in the hierarchy in Power BI

When you click on this button, the visual shows you quarters (assuming that you were on the year-level before this).

The quarters above are not for a particular year. They are the accumulation (or whatever the default summarization of the value field is) of the SalesAmount for all years. It looks like you created a new visual which only has the quarter in the X-Axis regardless of the year or higher levels of the hierarchy. This is a great way to see quarter 2 is the best-seller and quarter 3 is the worst.

If you do this action once again, this time you see the analysis on a monthly basis. Again this is regardless of year or quarter.

This is a great way to compare values in another level of hierarchy regardless of the higher levels. That is why I call this branching than drill down. You can use the same drill up button to go back to the higher levels.

Drill down setting is persistent

A question that I often get after explaining these in my courses is that: “If we apply any changes like above, and then publish the report, does it keep our changes, and users will see it at that view?” The answer is yes. The drill-down setting is persistent. If you drill down to the month level and publish your report, the default view of your report will show the visual at the month level. However, users can change it for themselves.

Drill down/up is an end-user option

A very critical understanding is that the drill-down/up options are not developer-only features. End-users can also change it. And It would be very helpful for them to do it. This means for every data exploration they don’t need to come to you as a developer and ask for it to be implemented, they can do it all themselves.

Because the drill-down options are doing totally different actions, I strongly recommend that you include that in your end-user training and ensure they all understand how those options work.

Drill down/up menu available elsewhere

Drill down/up is not available only through those buttons I explained here. They are also available when you right-click on a column (or bar, or data point) in the chart;

They are also available in Power BI Desktop under the Data / Drill menu option;

Drill-through is different

You may have seen options for drill-through and may think that is the same. Drill-through is another way to drill down from a higher-level view of the dashboard to a lower-level view of the detailed visualization. This type of analysis can be done differently using drill-through, which I explained it in detail here;

Summary

Power BI has a good range of drill-down and up options, which can be helpful in data exploration, analyzing the trend, and branching. In this article and video, I explained how these options are working. The important tip to consider is that the drill-down and up are end-user options, you have to ensure that you included that in your end-user training to avoid their confusion.

Download

Download the sample file to practice from here.

    Enter Your Email to download the file (required)

    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    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 nine 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.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
    He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
    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.

    Leave a Reply