Better Drill Down Experience with Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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;

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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.

7 thoughts on “Better Drill Down Experience with Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *