Level 8 · Hierarchies and drill-down

Greg doesn’t need three charts. He needs one chart that knows years contain quarters and quarters contain months. That knowledge lives in the model, as a hierarchy.

What this practice level is

This is a quiz in a practice tool built for this site; Power BI Desktop runs only on Windows, and drill-down is something you click in a visual. The steps below are the exact ones to follow in Power BI Desktop.

What a hierarchy is

A hierarchy is an ordered set of columns from one table, from the most general level to the most detailed:

Hierarchy	Levels
Calendar	Year > Quarter > Month > Day
Product	Category > Subcategory > Product name
Geography	State > City

The columns already exist; the hierarchy only adds the order. Put it on a chart and the chart gets drill-down: the same visual can show any level, and the levels below are filtered by the level you clicked.

Building one in Power BI Desktop

1. In the Data pane, right-click Year in the Date table → Create hierarchy. A new item called “Year Hierarchy” appears with Year as its first level.
2. Right-click Quarter → Add to hierarchy → Year Hierarchy, then the same for Month. (In Model view you can also drag the columns into the hierarchy.)
3. Rename it, for example “Calendar”.
4. Drag the hierarchy onto a column chart’s X-axis well.

On the chart, the arrows at the top (or right-click a column) give Drill down (click a bar to go into it), Go to the next level and Expand all down one level, plus Drill up to go back.

Sort the months

'Date'[Month] holds “Jan”, “Feb”… Text sorts alphabetically, so a chart would show Apr, Aug, Dec first. In Table view select the Month column, then Column tools → Sort by column → Month Number. Harbor & Pine’s Date table already has that sort set; yours will need it.

What about the automatic date hierarchy?

With File → Options and settings → Options → Data load → Auto date/time on, Power BI builds a hidden date table and a Year > Quarter > Month > Day hierarchy for every date column. It’s handy in a quick prototype, but each one is a separate hidden table, it can’t be shared across fact tables, and it adds size to the model. With a marked Date table you turn it off and build one proper hierarchy yourself.

Common mistakes

• Fields in the Legend instead of the axis. That splits bars into colours; it doesn’t create levels.
• Levels in the wrong order. Month above Year makes “Jan” contain every year.
• Columns from different tables. A hierarchy’s levels all come from one table, which is one more reason to keep Year, Quarter and Month in the Date table.