Power BI hierarchies: levels that turn one field list into a path
Power BI hierarchies arrange related columns into levels, so a visual can move from a broad group to a narrow one. Year, quarter, month and day form the best-known example. A hierarchy is metadata about columns in one table, not new data. This guide explains the kinds you will meet, the automatic date version and the design choices that keep drill paths reliable. In short, right-click a column in the Data pane and create a hierarchy, add the lower levels, then drill down in any visual that supports it.
- Levels inside one table
- Date hierarchy explained
- Reliable drill paths
Last updated
What are Power BI hierarchies?
Microsoft describes a hierarchy in a tabular model as metadata. It defines a relationship between two or more columns in a table. For example, a calendar hierarchy places the year at the top, with month and day beneath it.
The purpose is a better experience for report authors. A table can hold dozens of columns in no clear order. A hierarchy presents a few of them as one simple object, so an author adds every level to a visual in a single move.
Each level stands for a column. Therefore you can rename a level to make it clearer, and the column underneath keeps its own name. A column can also appear in more than one hierarchy.
Three kinds of hierarchy an author works with
Microsoft's articles describe these three forms, and each one behaves a little differently in a visual.
Model hierarchy
A modeler defines it in the semantic model from columns of one table. It then appears in the field list as a single object with its levels.
Automatic date hierarchy
Desktop can generate one for a date column through the auto date/time option. It contains year, quarter, month and day levels.
Informal hierarchy
An author places two or more fields on the same axis of a visual. Those fields then act as levels, although the model defines nothing.
How does the automatic date hierarchy in Power BI behave?
When the auto date/time option is on, Desktop creates a hidden date table for each qualifying date column. The conditions are specific. The table must use Import storage, the column must hold a date type, and it must not sit on the many side of a relationship.
Authors then see a calendar icon on the date field. When they expand it, they find a hierarchy with four levels. A visual can use the whole hierarchy or single levels, and the author can also switch back to the plain date column.
However, Microsoft lists real limits. The levels follow calendar years only, and you cannot add weeks or a fiscal year. Each hidden table also adds to model size. So the guidance recommends this option for simple models and quick exploration.
Power BI hierarchies: automatic date levels versus a date table
Microsoft's guidance compares the convenient option with a date table that the team designs itself.
| Question | Automatic date hierarchy | Own date table |
|---|---|---|
| Who builds it? | Desktop, when the option is on | The modeler or the data warehouse |
| Which periods? | Calendar year, quarter, month, day | Any the business needs, such as fiscal periods |
| One filter for several fact tables? | No, each date column gets its own hidden table | Yes, one table can relate to many |
| Visible to the modeler? | No, the tables stay hidden | Yes, like any other table |
| Works in Analyze in Excel? | No | Yes |
Which design rules keep Power BI hierarchies reliable?
First, keep the columns in one table. Microsoft states that a hierarchy cannot span columns from more than one table. That is one reason the star schema guidance favors a single dimension table over a chain of small ones.
Second, make sure each level truly nests. A month name without its year adds up every January together. Microsoft warns about exactly this for the automatic date levels. Therefore filter or group by year whenever a lower level appears alone.
Third, name the hierarchy and its levels in business language. Readers meet those names in the visual title as they drill. Also remember that a hierarchy is not a security feature, since it inherits whatever access the model already grants.
Six checks before you release a new hierarchy
Run these checks in a test visual first, because a broken level misleads readers quietly.
- Every level comes from the same table
- Each lower value belongs to one parent only
- Level names read clearly without the column list
- Sort order suits months and other ordered labels
- Drill up and drill down both return sensible totals
- Unused raw columns stay hidden from report authors
Power BI hierarchies: common questions
Can a hierarchy in Power BI use columns from two tables?
No. Microsoft states that a hierarchy rests on columns within a single table. When the levels live in separate tables, bring them together first. For example, combine the product, subcategory and category tables into one dimension table, or add the parent value to the lower table with a column.
Should I turn the auto date/time option off?
It depends on the model. Microsoft recommends keeping it only for calendar periods and simple time needs, or for quick exploration. When a source already has a date dimension, the guidance says to use that table instead. Note that turning the option off removes the hidden tables, so fix affected visuals afterward.
What are inline hierarchy labels?
They show the labels of every level together as you expand a visual. For example, a bar reads as product and country side by side. With these labels you can also sort by the hierarchy itself. However, Microsoft notes that the built-in time hierarchy sorts by hierarchy order only.
Do Power BI hierarchies control who sees which data?
No. Microsoft is explicit that a hierarchy is a tool for a better user experience, not a security mechanism. It cannot grant access to objects a user lacks already. So handle access with roles and row-level security in the model, and treat levels purely as navigation.
Can Power BI Agency redesign our date and product levels?
Yes. One of our consultants reviews the dimension tables, builds a proper date table where one is missing and defines clear levels for drilling. The work happens online inside your own Microsoft tenant. Power BI Agency is a division of Sataware Technologies, independent of Microsoft, and begins with a free 30-minute assessment.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.