Some time ago, I wrote a post about how to handle Parent/Child hierarchies in PowerPivot 1.0. It was pretty intricate, because P/C are not handled natively in PowerPivot. Now, with the advent of Denali, Microsoft gave us all the functions needed to handle P/C in a convenient way. Kasper has already wrote a nice post about P/C here. His post is very informative and I suggest you reading it to look at all the P/C functions. In this post, I will need to repeat something he has already said but, hopefully, I will add some more insights into the DAX language solving a small issue that still exists with Parent/Child hierarchies, at least in CTP3.

Let us start with the data model. We have this data model (wow, take a look at how wonderful is the the new graphical view of tables in PowerPivot… great job guys!):

where the Hierarchy table contains this unbalanced P/C hierarchy, using the classical ParentID data model:

In order to show this P/C data structure in a PivotTable, we need to understand how DAX implements P/C handling. It is based on some very powerful functions, the most important of which are:

**PATH**: computes the full path of a node following the ParentID column, starting from the root **PATHITEM**: extracts an item from a path, as returned by PATH **LOOKUPVALUE**: searches for a value inside a table, following a relationship that is valid only inside the formula

Thus, by means of defining some simple columns:

- HierarchyPath = PATH([NodeId], [ParentNodeID])
- Level1= LOOKUPVALUE ([Node], [NodeId], PATHITEM ([HierarchyPath], 1))

You get this result, which is self-explaining:

In order to have the Parent/Child hierarchy work, we still need to create a user define hierarchy putting the various Levels, from 1 to 3, inside a single hierarchy:

Et voilà, the work is done. We can put this hierarchy in a PivotTable, expand all the levels and we get this result:

There are both good and bad news here.

- The good news is that all the levels, once put into a hierarchy, behave exactly like a Multidimensional P/C.
- The bad news is that if the hierarchy is a ragged one, as it is often the case with P/C, Denali CTP3 still makes a load of empty nodes visible.

SSAS handles this scenario with the usage of the HideMemberIf property of hierarchies, which hides useless nodes from the final result shown by the PivotTable. In CTP3, there seem not to be a way to set the HideMemberIf property for hierarchies, leading to this unwanted behavior. Hopefully, in the final release of Tabular the HideMemberIf will be available (if you are interested in having it, please vote this connect item, to make Microsoft guys aware of how useful this feature would be). If the item will be solved, then the next part of this post will be useless and life much easier. Thus, you might want to follow that item to check for its solution.

Nevertheless, if we want to mimic the HideMemberIf feature in CTP3, we still need to make some work with DAX. So, let us roll up our sleeves and start thinking at a viable solution.

The key to hide the unwanted rows is to note that:

- We can define, for each node, its depth. Annabel and Bill have a depth of one, being root nodes. Catherine, being a child of Annabel, has a depth of two. Vincent, being a nephew of Bill, has a depth of three. The depth can be defined at the row level of the Hierarchy table.
- We can define, for each cell, a Browse depth which is the depth up to which the P/C hierarchy has been unfolded, to make that cell visible.

With these definitions in mind, our PivotTable can be seen in this way:

If we hide, from this PivotTable, all the rows where NodeDepth is greater than BrowseDepth, we will reach the desired result. In PowerPivot 1.0 this was a big pain in the neck, as can be seen in my previous post. In Denali CTP3, the solution is much easier and elegant, because there is a very interesting function available in DAX: ISFILTERED.

ISFILTERED gets a column reference and returns a Boolean value indicating whether the column is filtered in the current filter context or not. Now, it is easy to note that a cell with BrowseDepth of 1 has a filter on the column Level1 and no filter for Level2 and Level3. A cell with BrowseDepth of 2, will have a filter on Level1 and a filter on Level2, with no filter for Level3.

Thus, we can define the BrowseDepth measure with this formula:

BrowseDepth :=

IF (ISFILTERED ('Hierarchy'[Level3]), 3,

IF (ISFILTERED ('Hierarchy'[Level2]), 2,

IF (ISFILTERED ('Hierarchy'[Level1]), 1

)))

This measure will compute, for each cell, the browse depth. We are still missing the node depth of each row. Another useful DAX function will help us: PATHLENGTH, which computes the length of a path column. We can add a calculated column NodeDepth=PATHLENGTH([HierarchyPath]) and get this:

Clearly, the NodeDepth cannot be aggregated using SUM, we need to use MIN. Thus, we define a new measure MinNodeDepth which simply aggregates the NodeDepth column using MIN. The final result, with all these measures in a PivotTable, can be seen here:

The final touch is to make the Amount measure BLANK whenever BrowseDepth is greater than MinNodeDepth, with this definition:

SumOfAmount :=
IF (
[BrowseDepth] > [MinNodeDepth],
BLANK(),
SUM (Invoices[Amount])
)

All the measures in a PivotTable:

And, after removing all the technical measures, we get the final result, which is nice as we wanted it to be:

Pretty easy, elegant and super fast, all of the requirements of DAX are met

I am confident that the SSAS team will take the time to let us use Parent/Child hierarchies with the HideMemberIf option. In the meantime this is a viable solution and a good exercise to start getting acquainted with the new DAX functions.

Last, but not least, in Tabular P/C hierarchies are standard user hierarchies. Thus, you can have as many P/C for a single table as you want. I know of many customers who will be happy to have more than one P/C hierarchy on the same dimension, without having the need to define separate dimensions as it was the case with Multidimensional in SSAS.

## Comments

## milang said:

I was just looking for this. Thanks :)

## milang said:

If you needed a measure (SumOfLeafAmount) that only shows childrens values, what would it be like?

In that case Annabel should show only the values for her leaf members (1200+800+600=2600) and Bill should show 1200 (400+500+300=1200).

## incognito007 said:

If reverse the order of the formular "BrowseDepth" as below:

if(isfiltered(level1),1

if(isfiltered(levle2),2

if(isfiltered(level3),3)))

It all return 1.

Why?

## AlbertoFerrari said:

@Incognito,

At BrowseDepth=2, both Level1 and Level2 are filtered. Thus, ISFILTERED(Level1) is always true, if the hierarchy is in the PivotTable. You need to start from the farthest node from the root with your tests to get meaningful results, because, at the leaf level, all levels are filtered.

@Milang,

Your "simple" question inspired my next blog post. Thus, the answer is coming but... as an innocent question as it looks, it is not very easy.

Stay tuned :)

## Alberto Ferrari said:

In my last post about Parent/Child hierarchies , there is a question, in the comments, that I found interesting.

## Stef said:

What about unary operators? How would you implement this very helpful functionality in tabular?

## AlbertoFerrari said:

@Stef,

I have already written about unary oeprators with DAX here: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/03/01/powerpivot-parent-child-and-unary-operators.aspx

The very same technique can be used in Denali, the only issue being in computing the sign of leaf level nodes at each browse depth, but this can be easily accomplished with DAX.

Hope this helps.

## challen said:

Hi Alberto, could you please show the sample on this link? my personal email is youhoo_Fu@hotmail.com http://sqlblog.com/blogs/alberto_ferrari/archive/tags/PowerPivot/default.aspx

Many thanks.

## Stef said:

Hello Alberto

thanks for your answer concerning unary operators. Now I would like to create something like a calculated member in the account hierarchy. Can you explain me how one can achieve this. Thanks a lot.

Regards

Stefan

## AlbertoFerrari said:

@Stef,

Well, your request is a very generic one, I'd need to understand your specific issue better to give any kind of answer, you can accomplish a similar behavior with an IF statement but... it really depends from the specifics of your need.

## Stef said:

With UDM and MDX I could create KPIs for example the ratio of two real members of the [Account] dimension (total net revenue / total FTE) and add the calculated member to the account hierarchy. I would like to achieve something similar with tabular model

## AlbertoFerrari said:

@Stef,

It can be done using IF inside a measure. Not very elegant, but it can be done... if you need the complete solution, I need to look at your workbook to write one.

In general, in Tabular you will need to write several measurea and then a single one using IF or SWITCH to choose the right value to show at each level, so to mimic calculated members.

## Karthik said:

Hi Alberto,

Thanks for this post.

I have a scenario where I use P/C hierarchy to construct and browse by Organisation Structure in the Employee dimension itself.Structure looks like below:

¦EmployeeID¦EmpName¦OrganisationID¦ParentOrgID¦

Issue is that in such a case, every ParentOrgID need not occur in the OrgID column,(i.e. every org unit need not have an employee directly assigned to it). So, the PATH function does not work and instead gives the error: "Calculation error in column 'dimEmployee'[]: The value '1209' in 'dimEmployee'[ParentOrgID] must also exist in 'dimEmployee'[organisationid]. Please add the missing data and try again."

The function description on msdn points out that "Values in parent_columnName must be present in ID_columnName." But as in this case, the business scenario can be different unlike the employee-manager structure. Can you think of any solution for this ? Thank you.( I can think of modelling as two different dimensions and hiding the blank ones as in your post, but I have a feeling that this kind of parent-child scenario should have been supported by the PATH function as well)

## AlbertoFerrari said:

@Karthik,

A quick answer is "you should model it as two dimensions: one P/C and another one as a classical dimension".

Another possible solution would be to use a calculated column to blank out links to non-existent parents.

It all depends from the data model you want the customer to browse...

## Francesco De Chirico said:

Hi Alberto,

what do you think about modify BrowseDepth formula in this way:

BrowseDepth := ISFILTERED('Hierarchy'[Level1]) + ISFILTERED('Hierarchy'[Level2]) + ISFILTERED('Hierarchy'[Level3])

avoiding the use of the IF() functions?

## AlbertoFerrari said:

@Francesco,

This looks a great definition for the formula, much easier to write and mantain. Like it very much! :)

Thanks for sharing this.

## skiwi said:

so essentially we have to hard code how many levels there are?

if so this is a massive problem and will mean that many will have to use regular SSAS. what if you work for an organisation of 100,000+ people where there could be 20 or more levels...

## txeriff said:

Hi Alberto,

Thank you for your post! it helped me lots.

I have a short question:

I have this hierarchy;

http://social.msdn.microsoft.com/Forums/getfile/192856

For some reason the first row, area 30 its doing 36-22=14.

22 its the value from SQL Query to the tables.

No clue why its doing subtraction

## Juan José Macias said:

Hola,

Yo para eliminar los espacios en blanco aplico las siguientes formulas para cada nivel:

Level 1:

LOOKUPVALUE(Hierarchy[Node];Hierarchy[ParentNodelID];PATHITEM(Hierarchy[HierarchyPath];1;1))

Level 2:

IFf(ISBLANK(PATHITEM(Hierarchy[HierarchyPath];2;1)); "(" & LOOKUPVALUE(Hierarchy[Node];Hierarchy[ParentNodelID];PATHITEMREVERSE(Hierarchy[HieararchyPath];1;1))&" datos)"; LOOKUPVALUE(Hierarchy[Node];Hieararchy[HierarchyPath];PATHITEM(Hieararchy[HieararchyPath];2;1)))

Level 3 y sucesivos:

La fórmula sería igual pero en vez de 2;1 indicar 3;1 , 4;1 y así hasta el último nivel.

Un saludo y gran blog.

## Eric said:

Thanks ! Very good analyse and elegant solution (it is just a shame that MS does not work naturaly like that...)

btw the PATHLENGTH work only with 'piped' full path.

I usualy use /, for a better human user comprehenssion.

If you are in the same situation, just use a Dax SUBSTITUTE (PATHLENGTH(SUBSTITURE([HierarchyPath];"/";"|")

## ziy said:

i just wanna know weather ferrari has a tall or hierarchical organizational structure

thank you

## Keith Barney said:

I wanted to try out your instructions so I created a hierarchy in the employee table following the instructions from Kasper De Jong in the link you provided. I was able to create the hierarchy (4 levels) but when I try to select in the field list of a new pivot table, I get his error: "The command was cancelled". Any suggestions?

## Abhinav Bisht said:

I am trying to do the same in Excel 2013 powerpviot model, but somehow it doesnt work for me, the only differnce i have is that i am doing this on the via the tabular Model on the BIDS and writing formula on the model as the new table column any insights?

## Abhinav BIsht said:

What i mean is that the ISFILTERTED function always returns me false for all rows, and i cant get what does the isfiltered acutally does,

i have created excatly the same level1 level2 and levl 3 fileds as required here

## Dan said:

Just discovered some odd behaviour in Powerview hierarchy when using the ISFILTERED function to derive the BrowseDepth. In an Excel Pivot Table using Powerpivot data, the hierarchy displays with the correct BrowseDepth, however in Powerview the BrowseDepth is equal to the depth of the hierarchy, not the current visible depth. I am flagging this up to Microsoft as it appears to be a bug in Powerview.

## Dan said:

I have discovered that due to Powerview issuing DAX and Excel PivotTables issuing MDX against the Tabular Model, there are some semantic differences between the functions you can use in both. ISFILTERED doesn't have a semantic equivalent in DAX compared to MDX, therefore when creating the same tables in Powerview and Excel, the BrowseDepth is different.

A suggested alternative is using COUNTROWS(FILTERS... as follows :

BrowseDepth:=

IF (COUNTROWS(FILTERS('Employees'[Level3])) = 1,3,

IF (COUNTROWS(FILTERS('Employees'[Level2])) = 1,2,

IF (COUNTROWS(FILTERS('Employees'[Level1])) = 1,1

))))))

However this only seems to work where the hierarchy goes down to the full depth. For an unbalanced hierarchy this formula gets it right for Bill's side of the hierarchy but not Annabel's.

## Dan said:

There is no workaround for this issue in Powerview, although Microsoft are planning to release the equivalent of HideMemberIf in the Tabular model in a coming release.

## Fred Lorrain said:

In the post of Kasper there is a screenshot that show the Index of every node and leaf, eg 0 - Dutch Treats Inc or 04 - HR

How do you do that ?

## Raghunath said:

•Level1= LOOKUPVALUE ([Node], [NodeId], PATHITEM ([HierarchyPath], 1,1))

Please use this when you are creating the named column

## Bill MacLean said:

I always like your excellent articles, thanks for the great tips.

One problem with most hierarchical displays is that you have to do "mental math" to determine the amount posted directly to the node that is collecting amounts lower in the hierarchy.

For example, what if Annabel herself sold $500? The total for the "Annabel" node would be 1800. I would like to display those numbers like this:

Annabel (Aggregate) 1800

Annabel 500

Catherine 400

Harry 600

Michael 300

Now it's easy to see that Annabel's whole team accounted for 1800, and of that 1800, Annabel herself was responsible for 500.

I can't figure out how to do this. Do you have any ideas?

Thanks,

Bill

## Vadim Gerya said:

Hi Alberto!

Thanks a lot for great approach !!!

What can we do with

BrowseDepth :=

IF (ISFILTERED ('Hierarchy'[Level3]), 3,

IF (ISFILTERED ('Hierarchy'[Level2]), 2,

IF (ISFILTERED ('Hierarchy'[Level1]), 1

)))

if number of levels is unknown at design time?

## AlbertoFerrari said:

@Vadim, P/C do not work in Tabular (or Power Pivot) if the number of levels is unknown at design time. You will need to choose a max depth and design the model with that in mind. In my experience, no hierarchy should ever exceed 10/15 levels...

## Andrew said:

Hi Alberto

I am a little confused. I am trying to mimic your example above along similar lines with 2 tables just as you have in the example. I have created the NodeDepth on the Hierarchy Dimension as a calculated column and that works fine. Should the BrowseDepth and MinNodeDepth be measures on the fact table (in your case the Invoices table).

Also I assume the SumOfAmount measure must also be on the Invoice table.

For some reason the min(NodeDepth) always seems to give me 1. Can you explain which table the MIN(NODEDEPTH) should be created on, and confirm it is a measure and not a calculated column.

I just want to clarify what is a calculated column, what is a measure and which table they should be created on.

Thanks

Andrew

## Andrew said:

Hi Alberto

I am a little confused. I am trying to mimic your example above along similar lines with 2 tables just as you have in the example. I have created the NodeDepth on the Hierarchy Dimension as a calculated column and that works fine. Should the BrowseDepth and MinNodeDepth be measures on the fact table (in your case the Invoices table).

Also I assume the SumOfAmount measure must also be on the Invoice table.

For some reason the min(NodeDepth) always seems to give me 1. Can you explain which table the MIN(NODEDEPTH) should be created on, and confirm it is a measure and not a calculated column.

I just want to clarify what is a calculated column, what is a measure and which table they should be created on.

Thanks

Andrew

## Andrew said:

Got it - 3 new measures on fact and 1 calculated column on Dimension.

Thanks

Andrew

## John said:

OK this is great.

I actually bought the online training and this is covered in more detail there (btw Andrew they are all calculated mesures of the DIMENSION not the FACT, that confused me too till i did the training)

Not sure if it has been answered already, but the solution needs a measure created on the same dimenion as the ragged hierarchy.

Does this mean then that you cannot pivot on 2 ragged hierarchies at the same time? My application needs a ragged Account dimension (which most account dimensions are) and a ragged Cost Centre dimension, how can I pivot on both?

## Dino said:

Great and very helpful article Alberto.

I am busy implementing KPI's that will be available to all client tools from Tabular thus centralising the business logic.

My scenario: I have a UDM in PoverPivot (Excel 2013)with one table (DimKPI) that is not related to any other table. This table contains levels for Function, KPA and KPI. (Your hierarchy works miracles here, Thanks)

[KPI01]..[KPI50] measures are calculated in this table (DimKPI). eg. KPI01:=CALCULATE(DIVIDE('FT1'[column], 'FT2'[column]))

Pivoting and filtering work 100% on all dimensions in Excel except when I drop the KPI hierarchy in the rows of the PivotTable.

The grand total for [KPI01], etc. is repeated for all nodes, including (blank), in the hierarchy. (? No relationships).

I have a hidden calculated column [IndicatorValue] in DimKPI as follows: =SWITCH([RefNo], "KPI 1", [KPI01], "KPI 2", [KPI02]...)

An Indicator:=IF([BrowseDepth]<=[MinNodeDepth],CALCULATE(AVERAGE([IndicatorValue]))) measure with KPI ability is based on this column. The values associated with the hierarchy rows are then shown correctly BUT filtering does not work on this result and formatting does not carry through from the original [KPI01]..[KPI50] measures.

When swapping the PivotTable rows with another dimension (DimDate or DimGeography) the [KPI01]..[KPI50] measures are shown correctly also with filtering applied BUT NOW the [Indicator], [Indicator Status] and [Indicator Goal] measures are repeated for each dimension item and filtering the results is igonred.

I am totally at a loss on how to solve this and any input will be much appreciated.

## Dino said:

Hi Alberto. I managed to solve my problem. I moved the [Indicator] formula to the Measures section and everything worked.

Best regards and thank you.