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