What Is a Hierarchy in Power BI?
A hierarchy in Power BI groups related fields — such as Year > Quarter > Month > Day, or Country > State > City — into a single drillable unit in the Fields pane. Hierarchies enable drill-down and drill-up within visuals, letting users explore data at different levels without separate charts for each level.
How to Create a Hierarchy in Power BI
Two ways to create a hierarchy in Power BI Desktop:
- Drag-and-drop: In the Fields pane, drag one field (e.g., Month) onto another (e.g., Year). Power BI creates a hierarchy. Continue dragging to add levels.
- Right-click > Create Hierarchy: Right-click a field, select Create Hierarchy. Then right-click other fields and choose Add to Hierarchy.
How to Delete a Date Hierarchy in Power BI
Power BI auto-generates a date hierarchy for every date column. To remove: File > Options > Current File > Data Load, uncheck Auto Date/Time. Recommended if you have your own Date table.
Data Hierarchy Types and Examples
- Time: Year > Quarter > Month > Week > Day — most common, for date drill-down.
- Geographic: Country > State > City — for map visuals.
- Organizational: CEO > VP > Director > Manager > Employee — for org charts.
- Product: Category > Subcategory > Product — for sales analysis.
Hierarchy Chart and Hierarchy Visual
Any visual supporting categories — bar, line, table, matrix — can display a hierarchy. Drag the hierarchy into the Axis or Rows well. Drill icons appear in the visual header. For org hierarchies, use the Org Chart visual from AppSource or the Decomposition Tree.
Hierarchy Slicer in Power BI
The built-in slicer doesn’t support hierarchical drill-down. Install the Hierarchy Slicer custom visual from AppSource for a tree-style expandable slicer. Users expand Category > Subcategory > Product within the slicer itself.
Power BI Filter Hierarchy Order
Filters follow precedence: Report-level (all pages) > Page-level (one page) > Visual-level (one visual). Slicers act as visual-level filters. The most specific level takes precedence when overlapping.
Power BI Date Hierarchy — Week Level
The auto date hierarchy omits weeks. Create a custom Date table with a Week Number column (WEEKNUM in DAX or Power Query), then build a manual Year > Quarter > Month > Week > Day hierarchy.
Parent-Child Hierarchy Visualization
For parent-child data (ID + ParentID), use DAX PATH(), PATHITEM(), PATHLENGTH(), and PATHCONTAINS() to flatten into level columns. Create a hierarchy from those columns. The Decomposition Tree and Matrix with expand/collapse display this well.
How to Create and Use Hierarchies in Power BI
This tutorial shows how to create a hierarchy, use it for drill-down, delete auto date hierarchies, and build a hierarchy slicer. About 6 steps.
Prerequisites
- Power BI Desktop installed
- A dataset with fields forming a natural hierarchy (e.g., Date table with Year, Quarter, Month; or Geography with Country, State, City)
Step-by-Step
Step 1 — Create a hierarchy. In Fields pane, drag Month onto Year. Power BI creates a hierarchy. Rename it (e.g., ‘Time Hierarchy’).
[Screenshot: Fields pane showing Time Hierarchy with Year and Month levels]
Step 2 — Add more levels. Right-click Day > Add to Hierarchy > Time Hierarchy. Reorder by dragging if needed.
Step 3 — Add to a visual. Create a bar chart, drag the hierarchy into Axis. Use drill icons in the visual header to navigate levels.
[Screenshot: Bar chart with drill-down icons highlighted]
Step 4 — Disable auto date hierarchy (optional). File > Options > Current File > Data Load > uncheck Auto Date/Time.
Step 5 — Add a Hierarchy Slicer (optional). Visualizations pane > ‘…’ > Get more visuals > search ‘Hierarchy Slicer’ > install. Add to report and drag a hierarchy into it.
Step 6 — Use in a Matrix visual. Create a Matrix, drag hierarchy into Rows. Click +/- icons to expand/collapse — like a Pivot Table with drill-down.
