Back in PowerPivot days, if I wanted to be able to drill down through a report by dates (Year, Quarter, Month, Day), I had to build the hierarchy and typically I had to build custom columns to hold these date parts. I would even have to do some fancy ‘Sort by Column’ changes to get the months of the year to display in the correct order. Power BI takes a lot of that work away and makes it easy to drill down through your data.
Let me start by loading some data from the Adventure Works Data Warehouse database. In the figure below, you can see that I’ve loaded several of the tables from this database, but for today I’m going to focus on just two tables, FactInternetSales and DimDate. The first thing I notice is that these two tables are connected with three relations represented by three lines, one solid and two dotted. This is because DimDate is a role playing dimension since there are multiple dates in the FactInternetSales table that could be associated with the date table to create reports.
By clicking on any of the relationships, I can see which fields are associated with that relationship since the field names will be enclosed in a box. In the figure below, you can see that the active relationship (the solid line) connects the DateKey field in DimDate with the DueDateKey in the FactInternetSales table.
However, I want to use one of the other relationships. To change which relationship is active, I can open the Data Tools Modeling toolbar and select the Manage Relationships button.
You can see that 6 active relationships have been set between the tables and two are currently inactive.
Suppose I want to use the OrderDateKey for my report. It would be nice if I could just click on the relationship that I want and like radio buttons (or option buttons) the previous relationship would be marked inactive and the new relationship would become active. Unfortunately, this just results in an error screen as shown here.
You have to first mark the relationship that you do not want as inactive by clicking on the yellow checkbox to the left of the relationship. Then you can select the relationship you want as shown in the next figure.
Now when I return to my Relationships page, the active connection is now linking the DateKey field in DimDate with the OrderDateKey field in the FactInternetSales table
I did this just to show that I can still work with the tables like I did in PowerPivot, but I really did not need to do any of this. If I open the Reports page and in the right side fields panel select first Sales Amount and then Order Date which is defined as the data type Date/Time, I will get a table that looks something like the following:
If I then switch to a stacked column chart, I will see a chart like the following (after a few formatting changes) that displays the Sales Amount by Year.
If I click on any of the columns, a dialog box appears with some details about that column. However, that is not what I want. I want to be able to drill down on any given year when I click on it.
In a relatively recent enhancement to PowerBI Desktop, several buttons have been added to the header. If I click on the icon third from the right as shown in the next figure, I can toggle the Drill Down feature. Drill down is turned on when the icon is mostly black instead of white.
Now when I click on the third column from the left (the 3rd quarter) the chart automatically updates to display sales by month and it displays the months July, August, and September in the correct order as shown next.
Clicking on the August column of this chart drills down to the next date level which is days in the month as shown here.
At any time, I can move back up the hierarchy by clicking the icon on the far left side of the header which is the Drill Up icon.
In fact, if I click on this icon twice, I can return to the Sales by Quarter chart. Now suppose I want a chart that displays all the months in the year. The button second from the left lets me drill down for all the current column on the current chart to the next level. So if I am displaying quarters, it show me the sales by month.
As you can see in the following chart, all the months in the year now appear. But perhaps more surprising is that the months appear in the correct chronological order, something we would have had to do with a Sort by Column option in a standard PowerPivot chart.
I hope you found that ability to drill down through a date hierarchy automatically without having to build that hierarchy first as interesting as I did. I’m at a SQL Saturday today in Tampa presenting an introduction to Power BI to the attendees. Maybe I’ll see you there.
C’ya next time.