Power BI Blog: Dating Part 4

Welcome back to this week’s edition of the Power BI blog series. This week, we create a hierarchy for Calendar1.

Cast your mind back to Power BI Blog: Dating Part 2, where we generated a simple Date Table using the DAX CALENDAR() function, which we called Calendar1. We used Calendar1 to link the other tables in our simple Data Model.

This enabled us to display the data from Costs and Sales correctly in a matrix visualisation:

We completed Calendar1 by marking it as a Date Table and adding more columns in Power BI Blog: Dating Part 3.

Although we have the columns we need for a hierarchy, the ability to drill down is not available in the matrix visualisation:

The date hierarchy is not automatically available because we turned off auto Date/Time in Power BI Blog: Dating Part 1. Therefore, we will need to create our own hierarchy. Users of older versions of Power BI may expect to be able to drag and drop Qtr in Year in the Fields pane, but this is no longer possible:

This feature was removed because users were accidentally creating hierarchies more often than they deliberately created them! Now, to create a hierarchy, we start by right-clicking on Year.

Choosing the option to ‘Create hierarchy’ we create Year Hierarchy, which contains Year:

We still can’t add to this on the Fields pane by clicking and dragging, but we can right-click on the next column in the hierarchy, which is Qtr.

We can select ‘Add to hierarchy’ and then ‘Year Hierarchy’ to add Qtr.

We can repeat this process for Month Number and Day.

Now we can drag Year Hierarchy to the matrix visualisation instead of Date:

Looking at the visualisation, we now have drill down options:

We can use these to view the data at different levels:

It’s getting better, but we need to refine the Calendar1 columns to make them easier to interpret. We will address this next time…

Check back next week for more Power BI tips and tricks!