PATHITEMREVERSE Function (DAX)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted backwards from right to left.
This function can be used to get an individual item from a hierarchy resulting from a PATH function.
This function reverses the standard order of the hierarchy, so that closest items are listed first, For example, if the PATh function returns a list of managers above an employee in a hierarchy, the PATHITEMREVERSE function returns the employee’s immediate manager in position 2 because position 1 contains the employee’s id.
If the number specified for position is less than one (1) or greater than the number of elements in path, the PATHITEM function returns BLANK.
If type is not a valid enumeration element an error is returned.
This DAX function is not supported for use in DirectQuery mode. For more information about limitations in DirectQuery models, see http://go.microsoft.com/fwlink/?LinkId=219172.
The following example takes an employee ID column as the input to a PATH function, and reverses the list of grandparent elements that are returned. The position specified is 3 and the return type is 1; therefore, the PATHITEMREVERSE function returns an integer representing the manager two levels up from the employee.
=PATHITEMREVERSE(PATH(Employee[EmployeeKey], Employee[ParentEmployeeKey]), 3, 1)