Ravindra BagaleCourses & study guides Track your progress

Guides

Hierarchies and Drill Down in Power BI

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

A hierarchy is an ordered list of columns from general to detailed (Year › Month, or State › City › Store). Drill down walks those levels inside one visual. It is not drill through to another page.

Friends! The owner asks “Maharashtra kitna, Pune kitna, Kothrud store kitna?” Why does one chart fail? Because all levels fight for space. How? We build a Location hierarchy on a fictional FreshBasket model, put it on a bar axis, and press the four drill buttons. Down stays on the page. Through is a different guide.

Quick answer

Vertical path:

  1. Create hierarchy: drag City under State (or Month under Year).
  2. Put the whole hierarchy on the visual axis or rows.
  3. Drill down (drill mode + click) follows one path.
  4. Go to next level replaces the axis with the child level for all.
  5. Expand keeps parents and shows children under them.
  6. Drill up climbs back. This is not drill through.

Real example: State → City → Store sales

State City Store Sales
Maharashtra Mumbai Dadar 600
Maharashtra Mumbai Andheri 400
Maharashtra Pune Kothrud 160

Plain bar of State shows Maharashtra 1160. That is fine for a headline.

What drill down does when you click Maharashtra in drill mode:

  1. The visual keeps Maharashtra’s path only.
  2. Next you see Mumbai 1000 and Pune 160.
  3. Drill into Mumbai → Dadar 600 and Andheri 400.
  4. Pune’s 160 is not on that path anymore.
  5. Drill up brings State back.

Why a hierarchy helper is needed: without levels stacked, the drill buttons stay grey. One field alone cannot walk down.

What do I need before this guide?

Before and after (look at the tables first)

Before hierarchy drill State total without drill levels.

Before

After hierarchy drill Cities after drill down from state.

After

Build the hierarchy

Hierarchy Levels A hierarchy is Year › Quarter › Month (or State › City › Store) so one visual can drill level by level. Hierarchy Levels A hierarchy is Year › Quarter › Month (or State › City › Store) so one visual can drill level by level.

A hierarchy is Year › Quarter › Month (or State › City › Store) so one visual can drill level by level.

  1. Data pane → drag City onto State (same table).
  2. Drag Store under City.
  3. Rename the hierarchy Location.
  4. Date table: Year › Quarter › Month › Date is the usual time hierarchy.
  5. Levels must live on one table. That is why City sits on Store, not on the fact table alone.

The four drill moves

Drill Buttons Four drill moves: drill down one path, go to the next level for all, expand one level, and drill up. Drill Buttons Four drill moves: drill down one path, go to the next level for all, expand one level, and drill up.

Four drill moves: drill down one path, go to the next level for all, expand one level, and drill up.

Drill Vs Expand Drill down keeps one path. Expand shows children under every parent still on the visual. Drill Vs Expand Drill down keeps one path. Expand shows children under every parent still on the visual.

Drill down keeps one path. Expand shows children under every parent still on the visual.

  1. Drill down (mode) — click one bar; only that parent’s children appear.
  2. Go to the next level — every parent is replaced by its children (all cities, no states).
  3. Expand all down one level — states stay, cities appear underneath.
  4. Drill up — climb one level.

What you should see after Expand on State: Maharashtra still listed, with Mumbai and Pune under it. After Next level: only cities on the axis.

Down versus through (say it out loud)

  1. Drill down — same visual, deeper grain.
  2. Drill through — another page, filtered to the item.
  3. Use down for “show me cities inside the state”.
  4. Use through for “open the store detail page with slips and phone bill”.

Mistakes and calm fixes

Symptom Likely cause Fix
No drill icons One field on axis Add hierarchy / second level
Wrong cities appear Used Next level vs Drill Pick the button that matches the sentence
Levels from two tables Hierarchy needs one table Put attributes on the dimension
Confused with through Mixed vocabulary Separate guides; separate clicks

Ravindra Bagale's Tip

Interview line: “Drill down stays in the visual; drill through opens a page. Expand keeps parents; next level replaces them.” Short and hireable. Got it?

Practice task

  1. Build State › City › Store.
  2. Try all four buttons on a bar chart.
  3. Write one sentence for Expand vs Next level.
  4. Drill up until State returns.

Learn it properly

Course lessons:

Related: Drill through · Matrix

Got it? Hierarchy sets the ladder; drill buttons walk it inside one visual. Next: field parameters. Let us go ahead.

Frequently asked questions

What is a hierarchy?

An ordered set of columns from general to detailed, such as Year › Month › Day, used for drilling in one visual.

Drill down vs expand?

Drill down follows one clicked path. Expand shows the next level under every visible parent.

Drill down vs drill through?

Down stays inside the visual. Through opens another page filtered to the item.

Why are drill buttons missing?

Only one field is on the axis. Add a hierarchy or a second level.

Can levels come from two tables?

A hierarchy lives on one table. That is one reason State and City sit together on a dimension.

Course lessons?

What a hierarchy is, and the drill buttons chapter.