PATHITEMREVERSE() and Unbalanced Trees

Recently I wanted to analyze some web log information, and decided to try it out in a BISM.  After getting the data loaded up, I noticed that I had not done any parsing on a field that indicated the page/control that was being logged.  This information was in an unbalanced hierarchical format, delimited with “/”.  I was really only concerned with the leaf level of this tree, as this is the information that needed to be reported to my end users.

After looking around for the right text function to parse this, i wasnt able to find a REVERSE() function.  As with most things in DAX, you will find a way to perform the operation if you take a step back and review what you are trying to do.  In this particular situation, I was trying to reverse a path, and there just so happens to be a PATHITEMREVERSE().  If you are not yet familiar with PATH(), PATHITEM(), these are functions that allow you to traverse a parent/child relationship.  So, in my example, I could parse the string in the following manner:

Field1:  /Controls/Screens/ScreenName

The first issue is that PATHITEMREVERSE() takes the input in the format of PATHITEM(), which is pipe delimited.  Our first task is to get the data into that format –

=SUBSTITUTE(table[Field1], “/”,”|”)

Output:  |Controls|Screens|ScreenName

Now, we can apply the pathing functions.

=PATHITEMREVERSE(SUBSTITUTE(table[Field1], “/”,”|”), 1)

Output:  ScreenName

With the path reversed, we chose to select the first node in the path, which was exactly what we were looking for.  If this were a balanced tree, we would not have needed to reverse it and we could have just used PATHITEM() and indicate that we were looking for node 3.  Using SUBSTITUTE() along with PATHITEMREVERSE() can also be used to search in reverse for a general string, as long as you replace what you are looking for with a |.