Better Drill Down Experience with Power BI

If you have used the drill down/up feature in Power BI, you know that it is a very powerful feature for exploring the data, you can expand a hierarchy to the next level, and go back. However, there is a little trick that can make your hierarchy exploration much better. Let’s check it out in this short blog post. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Prerequisite

For examples of this post, you need the FactInternetSales table from AdventureWorksDW example.

Explaining the Problem

After loading FactInternetSales into a new Power BI Desktop file, and creating a chart by SalesAmount and OrderDate (a field in the FactInternetSales which is a date field), you will see a chart like this:

Because the OrderDate is a date field, it automatically brings the date hierarchy of the default date dimension (if you like to learn more about the default date dimension, read this article). It means that now, you can click on the Drill mode of the chart and then with each click you will navigate to the next level.

Well. that looks pretty amazing, that with a click on a column (let’s say the year 2007) you can navigate to all quarters of that year. But wait for a second! there is an issue here. How would you know that we are in the year 2007 if I didn’t tell you a sentence ago!? Correct, there is no way to see the year you are in it, in the screenshot above. The drill-down experience that you see above can be enhanced much more if you add the higher level of the hierarchy in the navigation. let’s see how this is possible.

Solution

Here I explain only one of the many solutions that can make this experience easier. let’s follow instructions to see how it works.

Step 1: Navigate to the highest level of the hierarchy in the visual. Use the drill up button as much as possible to get to the highest level of hierarchy (in this case; year)

Step 2: Go to the Format tab of the visual

Step 3: Go to the axis that has the hierarchy field in it (in our example; X-Axis) and change the type to Categorical (if it is not Categorical already)

Step 4: In the same axis, scroll further down, and change Concatenate labels to OFF.

All done now. let’s see how it works.

Testing the Result

If you start drilling down and up this time, you will see the difference that it will make. You can see the value from the higher level of the hierarchy through the navigation;

And further you go through the hierarchy you can still see this information coming through;

Here is the full experience now;

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.