Dax Function: PATHITEMREVERSE

Category: Parent and Child Functions

The PATHITEMREVERSE function in Power BI is a DAX function used to retrieve a specific item from a hierarchical path, starting from the end of the path. It provides flexibility to navigate hierarchies in reverse order.

Purpose

The primary purpose of the PATHITEMREVERSE function is to facilitate hierarchical analysis by accessing levels in a path from the last node backward. It is particularly useful in scenarios where the hierarchy’s depth varies, and you need to analyze or extract data from the end.

Type of Calculations it Performs

  • Extracts specific nodes from hierarchical paths in reverse order.

  • Supports navigation through variable-depth hierarchies.

Practical Use Cases

  • Determining the most immediate supervisor in an organizational hierarchy.

  • Extracting the deepest level (e.g., subcategories) in a product hierarchy.

  • Identifying the last-accessed folder or file in a path.


PATHITEMREVERSE(<path>, <position>, [<datatype>])</datatype></position></path>
ParameterTypeDescription
pathColumnA column or calculated value containing the hierarchical path string.
positionIntegerSpecifies the position (level) to retrieve from the end of the path.
[datatype]OptionalDetermines the return data type: INTEGER or TEXT (default is TEXT).

How Does PATHITEMREVERSE Dax Works

The PATHITEMREVERSE function:

  1. Counts the levels in the hierarchical path.

  2. Determines the node corresponding to the specified reverse position.

  3. Returns the value at that position in the specified format (TEXT or INTEGER).

Example Formula

If EmployeePath is 1,2,3,4:

PATHITEMREVERSE(Employee[EmployeePath], 1) // Returns "4" PATHITEMREVERSE(Employee[EmployeePath], 2, INTEGER) // Returns 3 as an integer

What Does It Return?

The function returns:

  • The value at the specified reverse position in the path, either as a string (TEXT) or numeric value (INTEGER).

When Should We Use It?

  • When analyzing data from the bottom of a hierarchy upward.

  • In scenarios where hierarchical depth varies across entities.

  • For reverse-order comparisons or navigation.

Examples

Basic Usage :

Retrieve the last node from a path:


PATHITEMREVERSE("1,2,3,4", 1) // Returns "4"

Column Usage

Create a calculated column to display the immediate supervisor:


ImmediateSupervisor = PATHITEMREVERSE(Employee[EmployeePath], 2, INTEGER)

Advanced Usage

Combine with PATHLENGTH to dynamically extract levels:


DynamicReverseLevel = PATHITEMREVERSE(Employee[EmployeePath], PATHLENGTH(Employee[EmployeePath]) - 2)

Tips and Tricks

  • Understanding Reverse Indexing: The position parameter starts from 1, where 1 is the last node.

  • Consistent Delimiters: Ensure paths are properly formatted with commas.

  • Data Type Alignment: Use [datatype] to standardize results across calculations.

Potential Pitfalls

  • Specifying a position greater than the path length results in an error.

  • Incorrect or inconsistent path formats can lead to unexpected outputs.

Performance Impact of PATHITEMREVERSE DAX Function:

  • Pre-compute paths with PATH for optimal performance.

  • Use in conjunction with filters to limit large dataset calculations.

Related Functions You Might Need

  • PATH: Generates hierarchical paths.

  • PATHITEM: Retrieves nodes from the beginning of a path.

  • PATHLENGTH: Counts levels in a path.

Want to Learn More?
For more information, check out the official Microsoft documentation for PATHITEMREVERSE You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What is the PATHITEMREVERSE function used for in Power BI?

It retrieves a specific node from the end of a hierarchical path.

2. Is the PATHITEMREVERSE function 0-based or 1-based?

It is 1-based, meaning positions start from 1 at the end of the path.

3. What happens if the reverse position exceeds the path length?

The function returns an error.

4. Can PATHITEMREVERSE return numeric values?

Yes, by specifying the [datatype] parameter as INTEGER.

5. Does PATHITEMREVERSE support custom delimiters?

No, it works with paths formatted using commas as delimiters.

Previous article
Dax Function: PATHITEM
Next article
Dax Function: PATHLENGTH