30 Days to Success in Power BI: Day Twenty Drill Down Setup

Welcome back to day twenty of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day nineteen? If so, here is the link to refresh your memory.

Today we are going to demonstrate the drill down functionality in Power BI.  One of the key requirements for the drill down feature is the need for a hierarchy to allow us to drill up and/or down. The previous Hank Aaron data did not lend itself well to hierarchies.  One of the most common automatic hierarchies is the date hierarchy. When we have a date field in our data, Power BI will automatically generate a date hierarchy which consists of year, month, day, and quarter. That gives us four levels to drill up or down with.

Because it is common in business intelligence reporting to generate reports from the data warehouse, it seems only logical that we are going to use the AdventureWorksDW2014 version of sample data to demonstrate this functionality. The free sample data warehouse database can be download from Microsoft here.

For demonstration purposes, connect to the AdventureWorksDW2014 database and load the fact and dimension tables as shown in Figure 1 below.

Figure 1 – Load OrderDate Hierarchy and SalesAmount

Notice how OrderDate is split out into Year, Quarter, Month, and Day fields. This is a hierarchy. If we do not want to have one of these levels in our hierarchy we can remove it here by selecting the X on the right side of the field on the left side of Figure 1.  For example, maybe we do not care to get aggregate rollups for quarters, therefore we can remove the Quarter field from this hierarchy here. Thus our drill down will only consist of year, month, and day aggregations.

At the same time, I should mention that if we chose not use the drill down functionality on this particular field hierarchy and we just want to view the date as a date, we can switch back to a regular date field as shown in Figure 2.

Stay tuned to the next installment to see the drill down in action!

Figure 2- Selecting a Regular Date Instead

About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on April 16, 2018, in Business Intelligence, PowerBI and tagged . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: