The PATHITEMREVERSE function returns the item that occupies the indicated position in a string resulting from executing the PATH function, being the positions counted from right to left.
PATHITEMREVERSE(
path,
position
[, type]
)
- path: Text string returned by the PATH function.
- position: Integer number indicating the position of the element to be returned, counting the positions from right to left starting with 1.
- type: (Optional argument) Value that defines the type of the returned value: TEXT (or 0) if you want the result to be returned as a text string (default value) or INTEGER (or 1) if you want the result to be returned as an integer.
The PATHITEMREVERSE function returns a text string or an integer.
Positions are counted from right to left. The item located at the extreme right of the list -corresponding to the item itself- occupies position 1.
If a position less than 1 or greater than the number of elements in the path is specified, the function returns Blank.
If the indicated type is invalid, the function returns an error.
We start from the following table:
This table contains worker identifiers and the hierarchical relationship between them. The scheme of this hierarchy is shown in the following figure:
Once the path of each employee has been obtained, we can extract the item that occupies the position in the extreme right of the list -corresponding to the element itself- with the following calculated column:
pathitemreverse_1 = PATHITEMREVERSE(Employees[path], 1)
In this case, the type of the values to be returned has not been specified, so the function opts for the default option and returns texts. The function returns the closest parent element for all elements which, in the case of position 1, matches the element itself.
If we repeat the example selecting position 2:
pathitem_2 = PATHITEMREVERSE(Employees[path], 2, INTEGER)
In this case we have specified that the result is returned as integers. Note how the value corresponding to element 112 (which has no parent elements) is a Blank.