With the release of Tabular model / PowerPivot we have introduced DAX functions that work with a Parent Child dimension. In this blog post we will show you how to use these functions to set up a parent child relationships and using it in a hierarchy.
First of all we need a table that has a parent child relationship. In this case I loaded the adventureworks database where the employee table has an EmployeeKey column and a ParentEmployeeKey column.
The first thing we need to do is create a calculated column to determine the parent child path on the table. You need to use the DAX function PATH that will create a path of id’s from the current level until the top level.
For this example the DAX formula is: =PATH([EmployeeKey],[ParentEmployeeKey])
This results in the following column:
As you can see the column now contains the complete path of EmployeeKey’s from bottom to top stored as a single string value. In the selected column you can see that Employee with Key 1 reports to Employee with Key 18 reports to Employee with key 23 which reports to Employee with Key 112. A big benefit of this approach is that the complete parent child path is pre generated and stored inside the model and in memory.
To use the values in this column there are a couple of specific DAX functions :
- PathItem (Path, Position[,Type])
This will get the item from a specific Path column at a position and can return a string or a integer value depending on the type (string by default). - PathItemReverse (Path, PositionFromEnd[,Type])
This will get the item from a specific Path column at a position starting from the end and can return a string or a integer value depending on the type (string by default). - PathLength (Path)
Returns the number of items in the specified path column - PathContains (Path, Item)
This will return is will returns a TRUE of False if the Item exists in the specified Path. Used in Filter functions.
So how can we use these functions in DAX? Let’s start by creating a flattened column structure. For each level in the parent child we want to create a column. Unfortunately because there is no way to create dynamic measures we have to manually create a calculated column for each level. The biggest benefit about this approach is that it is lightning fast.
We use the Dax function PathItem for each calculated column to get the key of a specific level.
=PathItem([Path],1,1)
This will get the first level using the path level and cast the results to an integer since we want to lookup using and integer
For the second level we would use: =PathItem([Path],2,1)
And so on and so on.
But let’s say we don’t want to use Key’s in your level but the firstname and lastname of a employee. We can use the DAX function Lookupvalue to get these values from the employee in the second level:
=LOOKUPVALUE(DimEmployee[FirstName], DimEmployee[EmployeeKey],PATHITEM([Path],2,1))
& ” – ” &
LOOKUPVALUE( DimEmployee[LastName], DimEmployee[EmployeeKey],PATHITEM([Path],2,1)))
This will result in a table with a flattened hierarchy:
Of course you can now place these levels into a real hierarchy using the diagram:
And use the hierarchy inside Excel:
If you have a ragged hierarchy you might want to check if there is item at the current level, if there is nothing you can use PathLength to get the last item in the parent child and thus repeat the level. Otherwise use the values from the current level.
=if(PATHITEM([Path],2) = BLANK(),
LOOKUPVALUE(DimEmployee[FirstName],DimEmployee[EmployeeKey],PATHITEM([Path],PATHLENGTH([Path]),1))
& ” – ” &
LOOKUPVALUE( DimEmployee[LastName],DimEmployee[EmployeeKey],PATHITEM([Path],PATHLENGTH([Path]),1))
,LOOKUPVALUE(DimEmployee[FirstName],DimEmployee[EmployeeKey],PATHITEM([Path],2),1)
& ” – ” &
LOOKUPVALUE( DimEmployee[LastName,DimEmployee[EmployeeKey],PATHITEM([Path],2),1))
This will give the following results:
Another example of a Parent child function could for example be to use the Pathcontains function in a DAX query to return all employees that are in the organization together with employee with key 3.
This will look like this:
evaluate
FILTER (DimEmployee, PATHCONTAINS ([Path], 3))
There are some nice improvements in PowerP, really nice.
Hi Kasper, nice post.
I watched your below TechEd 2011 video for DAX .
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI408
I wonder if Micosoft is plan to consider “Dynamic Parent-Child level” feature in new PowerPivot release. In above post, you have created Level 1, Level 2, Level 3, Level 4 and Level 5 “manually” uasing DAX. In my reporting requirement, I don’t know number of level in parent-child releationship (in some instance it could be upto 3-levels or 8-levels or… ) and it has to be dynamic. Are you considering this option for future release or is there any workaround for existing PowerPivot version? Thanks.
Hello,
Thank you for your post. We have many plans but it is too early to comment on them. If you want your feedback to be heard please file a suggestion at http://connect.microsoft.com/sqlserver . This will get visibility to the AS team and will be noted.
Thanks,
Kasper
Kasper
thanks very much, that’s exactly, what i am searching for. i am downloading right now the RC 0, it will be perfect, if there is a function to return the level of the path automatically,
i understand that it is possible using VBA coding in excel or Recursive queries in SQL server, but i am not good in any of them.
thanks again
just installed
PATHLENGTH([path])
thank you very much 🙂
How to use this function when employee to parent employee relationship is dated
Hi,
can i use parent relationship in case that my hierarchy is based on text
for example
Department AAb18 is under Factory BD etc…
if i use path i receive the error
“Calculation error in column ‘FCT'[]: The value ” in ‘FCT'[Father] must also exist in ‘FCT'[Cost Center]. Please add the missing data and try again”
Hi @Eitan ,
Yes that should work. It works in my simple example. I feel like your parent child data is not really parent child but contains some holes.
Kasper
Thanks for your reply,
following your note i copy pasted the table (from the green environment) to a new excel sheet, the i created a linked table. now the path works. but still in the original file (in which the data is based on ODBC and there are links between files) i get the same error.
any idea?
thanks
@Rohit Acharya , if your hierarchy is dated, one solution is to created calculated columns where you concatenate the date and the hierarchy keys. I.e., you create columns EmployeeKeyDated and ParentEmployeeKeyDated, defined as something like DateKey & “_” & EmployeeKey, likewise for the parent key column. Then you have two columns that can be used to calculate a path on, and the PATH function doesn’t need to know that there are multiple hierarchies, one for each date. Hope that helps.
Hi to all.
Sorry if i will do a silly question about PARENT-CHID Dimension in POWERPIVOT scenario.
In Our company will buy as soon as possibile SQL SERVER 2012 but now ,i can do only with powerpivot last release.
(I used MULTIDMENSIONAL but i am studying also powerpivot to understand if some multidimensional project i will convert in tabular mode).
I have a dimension PARENT-CHILD to describe categories of PRODUCTS.
There are 4 levels examples product with code PIPPO has a CATEGORY like tHAT
E E 10 01
Every of that characters means a level (total level 6)
E means product of Elettrodomestic
EE Elettedomestic cheap
EE10 Fridges
EE1001 FrIDGES class A and so on
I have a table in my DW DB that descibes all these levels and i can attach to my powerpivot DB…I used that to create a parent child dimension in a Multidimensional scenario.
But the problem is how i can now represent this kind of dimension in a powerpivot…???
What works around i have to do ???
Will you have any examples …???
Think my classification of PRODUCTS like in ADVENTURE WORKS is PRODUCT CATEGORY…
Regards in advance…
How to do this on SCD Type 2 employee table? I get error in Path function because for same user id there exist 2 records with different manager ID’s.
Unfortunately that isn’t possible with the parent child functions
Kasper this a great DAX function. I’m trying to use this but my source data has some issues. Specifically I have [ParentEmployeeKey] values that are unexpectedly missing from my [EmployeeKey] column. (effectively meaning an employee reports to a manager that does not exist).
When I try to use PATH in this scenario I receive an error about missing keys. I tried to wrap the whole PATH function with ISERROR, but the calculated column still fails. How can overcome this problem without cleaning up my source data before I pull it in to PowerPivot?
Hi,
I like this article, But how can I acheive if one child have multiple parents.
Please let me know..the suggestion.
Thanks for the information. It works great. However, wouldn’t it be simpler to create a sql query, by using self joins to obtain the parent child information and then use power pivot to create the hierarchy? This eliminates the need for somewhat complex dax calculations.
Hi,
I am trying to retrieve a path from the self-referencing table using:
PATH(table[Id], table[parent_id]). PowerBI returns alert: The PATH function has found the following loop: ‘17349|17418|17349|17269’ in columns table[Id] and table[parent_id].
There are a couple of typos in the ragged hierarchy formula that is causing errors. Here are the typos:
1. There is a missing closing square bracket on Last Name in the last LOOKUPVALUE function.
2. The last two PATHITEM functions close the parenthesis too early.
Here is a tested working formula:
=if(PATHITEM([Path],2) = BLANK(),
LOOKUPVALUE(DimEmployee[FirstName],DimEmployee[EmployeeKey],PATHITEM([Path],PATHLENGTH([Path]),1))
& ” – ” &
LOOKUPVALUE( DimEmployee[LastName],DimEmployee[EmployeeKey],PATHITEM([Path],PATHLENGTH([Path]),1))
, LOOKUPVALUE(DimEmployee[FirstName], DimEmployee[EmployeeKey],PATHITEM([Path],2,1))
& ” – ” &
LOOKUPVALUE( DimEmployee[LastName], DimEmployee[EmployeeKey],PATHITEM([Path],2,1)))
Hope that helps whoever is trying to implement this nice solution by Kasper.