PowerPivot Denali: Parent child using DAX

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.

image

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:

image

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.

image

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:

image

Of course you can now place these levels into a real hierarchy using the diagram:

image

And use the hierarchy inside Excel:

image

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:

image

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:

image

evaluate
FILTER (DimEmployee, PATHCONTAINS ([Path], 3))