Parent Child Hierarchies in DAX

Previously I’ve blogged about parent child dimensions in multidimensional, and how they work both physically (see here) and how to create and configure them (see here). DAX offers this same functionality using formulas that Microsoft has generously created and given us. Kasper has blogged about these (here) but for my own purposes in retaining this information I’m going to blog about it here. 🙂

Essentially, DAX does with a function the same thing we do in multidimensional in regards to naturalizing out the hierarchy for performance improvements. Remembering that DAX runs mostly on memory, it makes it much more efficient to normalize out that parent child dimension and let xVelocity store it as its distinct values that it needs. The most efficient function for doing that is the PATH function. The PATH function takes the parent child dimension, and identifies each level that exists down to what it needs. An example is:

=PATH([EmployeeKey],[ParentEmployeeKey])

Like the USERELATIONSHIP function, the PATH function only takes two arguments: the child key and the parent key. After it’s input into a parent child dimension, your result looks like:

image

You can see how the PATH functions returns a delimited list of each level of the hierarchy. The next step is to normalize it out, via the PATHITEM function. The PATHITEM function returns the value of that value at a given position. An example:

=PATHITEM([PCPath],1,1)

This returns a value column containing the number of the top level of the hierarchy.

image

This is great, but by itself the number 112 doesn’t really tell us anything. What we really want to know is: “Who is 112?” to accomplish this we can use the LOOKUPVALUE function. Our revised formula looks like:

 

=LOOKUPVALUE(DimEmployee[EmployeeFullName], DimEmployee[EmployeeKey],PATHITEM([PCPath],1,1))

The LookupValue function returns the string value we specify when we tell it what the key value is. Now we can just return the employee’s name instead of the key value. Continuing this, populating it out for the remaining 5 levels we have in this hierarchy yields a table that looks like:

 

 image

We can now build a hierarchy in our design view, and we have a normalized out parent child hierarchy!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s