In this week’s update to the Power BI service, Microsoft added the ability to drill down into hierarchies. You can read the announcement here. There are some other cool additions as well.
I thought I would take Drill for a spin and share my thoughts.
How to Use Drill
In my Free Power BI account (I have a Free one and a Pro one for testing, etc), I have a dataset related to students selling chocolate for a school fundraiser. I decided to be really cryptic and called it Chocolate Sales.
Figure 1 shows the empty palette.
To start, I’ll choose my visualization, the simple Column Chart, shown by the arrow in Figure 1. That leads to Figure 2.
Figure 2 shows the empty Column Chart. I’ll click the box for the Teacher field, shown by the arrow. This leads to Figure 3.
The Teacher column now occupies the Axis of the chart. The next step requires some accurate mouse-work. You may want to stretch out a little. You don’t want to pull something. I’ll wait.
Cool. We want the drill path to lead from Teacher to Student Name First Last. So, when we click on a Teacher column in the chart, we want to drill to a column chart of the students in that class. To do that, we need to drag the Student Name First Last column into the Axis box UNDERNEATH the Teacher, as shown in Figure 4.
If done correctly, both Teacher and Student Name First Last will be in the Axis box, with Teacher on the top, as in Figure 5.
The order of the fields here is important. The field at the top of the list in the Axis box will serve as the top level of the hierarchy, ie the top level of the drill path. Each other field below it will serve as a deeper level.
EDIT: I just played around with this more and putting the Student Name First Last ABOVE Teacher in the Axis resulted in the proper behavior anyway. So, perhaps there is some cardinality estimation behind the scenes to ensure proper behavior.
You can add more than one, but we’ll just use one to keep it simple. Our next order of business is to add a measure. For that, we can just choose Sales Amount – Total from the Chocolate Sales table, shown by the Blue arrow in Figure 5. The result is shown in Figure 6.
Here we have a fabulous Column Chart. Feel free to take a moment and soak it all in.
Life is good. OK. The default behavior when click on one of the columns is to filter this page of the report according to the value in that column. For example, if we click on Mr. Roboto’s column, it will cross-filter all other visualizations on the page to just Mr. Roboto’s class, as shown in Figure 7.
You can see in Figure 7 that the columns for the other Teacher values have become transparent, making Mr. Roboto stand out. Thank you very much, Mr. Roboto, for helping me demonstrate just what I needed to. Thank you. If we had other visualizations on this page, they would be filtered for just Mr. Roboto’s class.
In order to make the new Drill feature work, we have to change this default behavior to something new. First, we can click on Mr. Roboto’s column again to clear that cross-filtering. This takes us back to Figure 8.
Notice the shiny new icon in the upper right of the chart, shown by the Red arrow in Figure 8. This is the Drill Down button. This changes the mode of the visualization to Drill Down mode instead of the default Cross-Filtering mode. I made those modes up. They are not official names or anything, they just seem to fit with the behavior.
In the upper left of the figure, shown by the Blue arrow, is the Drill Down Level button. We will come to that shortly.
For now, I’ll click the Drill Down button in the upper right, resulting in Figure 9.
Notice the Drill Down button in the Red rectangle. It has reversed itself: instead of a dark arrow on a light background, it is a light arrow on a dark background. This is the subtle indicator that the Drill Down mode of the chart has been enabled. Now, if we click on the column for Mr. Roboto, we get the result in Figure 10.
Instead of cross-filtering, we have drilled down one level from Teacher to Student Name First Last, showing the Sales Amount – Total for each student in Mr. Roboto’s class. Hazzah! We can then click the Drill Up button, shown by the Red arrow in Figure 10 to go back up to the Teacher level, as in Figure 11.
Now, the Drill Down Level button is shown by the Red arrow. This one will take us down to the next level, but apply no filtering. So, instead of getting to the Student Name First Last level for a particular Teacher, we get all of the students, as shown in Figure 12.
As you can see, there is a horizontal scroll bar on the chart now, indicating there are too many values to display in the space provided. This is because this chart now holds all students from all four classes.
That sums up the explanation of how to use this new Drill feature. You can turn off the Drill mode, and return to the default Cross-Filtering mode, by clicking the Drill Down button again.
I am glad this feature is there, for sure. The ability to drill up and down through hierarchical levels is a really important bit of functionality. I also acknowledge that this is just the first pass at it. The fact that the Power BI team is able to keep going with WEEKLY releases here is outstanding. I must confess that I like the way this works in Power View better, where Drill is not a mode for the visualization, but an ability you use by double-clicking instead of single-clicking. That way, you do not have to choose between cross-filtering and Drill. As it is currently, you get one of the other, but not both. I do like, however, the Drill Down Level that lets you go down a level without having to filter. That can be quite handy at times. It will be great, at some point, to be able to guide the Drill to a new report that you have designed for the purpose of being the next level of Drill. But that is getting more structured, for sure, maybe a bit too structured for the current Power BI vision.
I hope you will try this out yourself. Power BI is such an exciting offering from Microsoft. Take it for a spin. If you haven’t already, head over to PowerBI.com and get a FREE account.