Hierarchy Drill Through Solutions?

Carly Chaput

I feed my sheets into Power BI (pre-formatted gantts) and I need to create a way to drill down through levels of a sheet's hierarchy. Ideally, at least 4 levels of the hierarchy, Great Grand Parent, Grand Parent, Parent, Child, if you will.

I'd like to mimic the concept that's in MS Project, on the View tab, Data group, Outline dropdown, where you can select what level of the outline to open up to, but have a smartsheet column that displays a number showing how deep in the hierarchy a task is so that when it feeds to PBI I can make a slicer that allows someone to drill through the gantt as if they were clicking through that drop down and opening the hierarchy level by level in MS Project.

Has anyone done this or does it sound possible? I'm not savvy with functions at all, so I'm not sure what to even try. Additionally, I am very open to any other work arounds to get this hierarchy drill through effect.

Kelly Moore

Hey @Carly Chaput

Yes, you can determine what Level in the hierarchy a row falls using the Ancestor function. Using the COUNT/Ancestor function will default your top level to zero. If you want it to begin at 1, add a +1 at the end of the formula.

Typically I put this in a helper column that I have very cleverly named "Level" 😀

=COUNT(ANCESTOR())

or if you like it to begin at 1

=COUNT(ANCESTOR())+1

Is this what you were looking for?

Kelly