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:
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:
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:
This returns a value column containing the number of the top level of the hierarchy.
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:
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:
We can now build a hierarchy in our design view, and we have a normalized out parent child hierarchy!