Drill Down Drill Down is a really useful feature which we can use on hierarchical fields. So date fields for example where we have different layers such as year quarter month and day.

We can funnel through each of the layers in turn and explore the data through those different time periods.

So instead of having for instance four different charts, one which may be shows the total amount by year by quarter by month by day.

Drill Down allows us to do that in one single chart and explore the data by going up and down the layers as we choose.

Let's do a workout and see how to use Drill Down in Power BI.

So I'm just going to get an Excel workbook here and going to use module 5 lesson 1B.

And bring that into my report.

So from here, I'll choose the credit transactions worksheet and we can see here we have one column which uses the transaction date, so I'm just going to click on load.

And load that into my Power BI report.

The first thing I'm going to do here is create a basic column chart using the transaction date and the amount.

So I'll expand the table there, I'll choose transaction date. I'm just going to click on transaction date just to bring in the full hierarchy and choose amount and I'll bring it into a clustered column chart there and it's defaulted to giving me the sum for each year. Now if I wanted to also look at the total by month total by quarter total by day, I could create three other charts to do that, but because it's hierarchical field, all I have to do is turn on Drill Down. Drill Down is available on a hierarchical field. It's a little arrow that will sit at the top or the bottom of the chart and if I hover over here, there it is, and I'm just going to click once to turn it on.

What will happen now is when I click on a column from my chart, so for example, 2020 it will Drill Down into the data for just that year it's like a filter, So if I click on it here the chart has changed. So I'm now looking at the four quarters, but only for that particular year. In fact, if I hover over you'll see here it reminds me that I'm looking at quarter three for 2021.

Now let's say I click on quarter three here again, it drills down to the next layer of the hierarchy. I'm now looking at July, August, September the months in that particular quarter. And again if I hover over it will remind me what exactly I'm looking at there 2021 quarter three. I've one more layer to go click on that and it's brought me to the day. So each of those different days within the month of August for 2021 can see here August the 16th has been a particularly good day and again hover over there it gives me the detail that I need. So that's a drill down on a hierarchical field.

We can easily go back up the layers just by clicking on this Arrow, so just click up here back to the months the quarters and back to the start again. A really easy way to explore the data for yourself. Just click on a column or a bar and it will just filter its way through just gonna bring it back up to the top here again and just have one look here up this icon. So this one oh, sorry, this one here expand all down one level and the hierarchy. So if I click on that, what it will do is it will show me this level as well as the next level down. I wouldn't do this too many times as maybe too much data for all four layers in the one go. But again, it just gives me another view of the data.

Again, just click here to come back to the top.