Parent/Child Hierarchies in Tabular with Denali

re: Parent/Child Hierarchies in Tabular with Denali

milang — Tue, 19 Jul 2011 10:26:26 GMT

I was just looking for this. Thanks :)

re: Parent/Child Hierarchies in Tabular with Denali

milang — Tue, 19 Jul 2011 13:53:28 GMT

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).

re: Parent/Child Hierarchies in Tabular with Denali

incognito007 — Tue, 19 Jul 2011 18:23:04 GMT

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?

re: Parent/Child Hierarchies in Tabular with Denali

AlbertoFerrari — Tue, 19 Jul 2011 19:03:12 GMT

@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 :)

Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wall

Alberto Ferrari — Tue, 19 Jul 2011 21:05:56 GMT

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

re: Parent/Child Hierarchies in Tabular with Denali

Stef — Sun, 24 Jul 2011 08:51:22 GMT

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

re: Parent/Child Hierarchies in Tabular with Denali

AlbertoFerrari — Sun, 24 Jul 2011 21:18:04 GMT

@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.

re: Parent/Child Hierarchies in Tabular with Denali

challen — Tue, 27 Sep 2011 02:04:13 GMT

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.

re: Parent/Child Hierarchies in Tabular with Denali

Stef — Wed, 19 Oct 2011 15:04:56 GMT

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

re: Parent/Child Hierarchies in Tabular with Denali

AlbertoFerrari — Wed, 19 Oct 2011 18:05:31 GMT

@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.

re: Parent/Child Hierarchies in Tabular with Denali

Stef — Sat, 22 Oct 2011 12:26:36 GMT

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

re: Parent/Child Hierarchies in Tabular with Denali

AlbertoFerrari — Tue, 25 Oct 2011 10:53:43 GMT

@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.

re: Parent/Child Hierarchies in Tabular with Denali

Karthik — Mon, 12 Dec 2011 14:44:34 GMT

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)

re: Parent/Child Hierarchies in Tabular with Denali

AlbertoFerrari — Mon, 12 Dec 2011 21:31:57 GMT

@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...

re: Parent/Child Hierarchies in Tabular with Denali

Francesco De Chirico — Thu, 09 Feb 2012 21:59:57 GMT

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?

re: Parent/Child Hierarchies in Tabular with Denali

AlbertoFerrari — Thu, 09 Feb 2012 22:07:04 GMT

@Francesco,

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

Thanks for sharing this.

re: Parent/Child Hierarchies in Tabular with Denali

skiwi — Thu, 19 Jul 2012 01:37:23 GMT

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...

re: Parent/Child Hierarchies in Tabular with Denali

txeriff — Mon, 12 Nov 2012 14:28:59 GMT

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

re: Parent/Child Hierarchies in Tabular with Denali

Juan José Macias — Sat, 09 Mar 2013 16:43:21 GMT

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.

re: Parent/Child Hierarchies in Tabular with Denali

Eric — Wed, 10 Apr 2013 18:14:19 GMT

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];"/";"|")

re: Parent/Child Hierarchies in Tabular with Denali

ziy — Sun, 19 May 2013 02:58:27 GMT

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

thank you