CODEWITHSUNDEEP
sql-serversql-server-2008olappowerpivot
picmate 涅
picmate 涅

Reputation: 4249

Self Joining a Table in Power Pivot

I would like to know whether Excel Power Pivot can handle a self join on a table. This is to derive parent child relationships from the data (I know this could be done in SSAS).

For example consider the following:

ID  Level         Individual       ImmediateParent
2   son            Tom               3
3   father         John              4
4   grandpa        Lee               5
5   great grandpa  Sam               6
6   root           Root

I would like Power Pivot, when producing the pivot table, to show only one raw when called for the level, for example son, and all the others to be contained with in it to drill-down and look.

Upvotes: 1

Views: 1911

Answers (1)

picmate 涅
picmate 涅

Reputation: 4249

My reason for a self join on a table was to derive parent-child relationships. But in powerpivot this could easily be handled with "PATH" dax formula. For example for the parent-child relationship in the question the dax formula would look like:

PATH(ID,ImmediateParent).

Here are some good tutorials on creating parent child relationships with the use of PATH formula:

Parent-child using dax

Powerpivot and parent-child hierarchies

Upvotes: 1

Related Questions