Dax Function: PATHITEM

Category: Parent and Child Functions

The PATHITEM function in Power BI is a DAX function used to extract a specific item from a hierarchical path generated by the PATH function. It identifies and returns a node at a specified position in the path.

Purpose

The primary purpose of the PATHITEM function is to enable analysis and navigation of hierarchical structures by isolating specific levels within a path. It simplifies accessing individual nodes or entities within a hierarchical path.

Type of Calculations it Performs

  • Retrieves a specific element (node) from a delimited string (path).

  • Supports hierarchical analysis by isolating levels dynamically.

Practical Use Cases

  • Extracting direct managers or supervisors from organizational hierarchies.

  • Identifying specific levels in product categories or folder structures.

  • Simplifying hierarchical data for visualization and reporting.


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

How Does PATHITEM Dax Works

The PATHITEM function:

  1. Splits the hierarchical path into individual components based on the delimiter (default is a comma ,).

  2. Extracts the node at the specified position.

  3. Converts the result to the specified datatype if provided.

Example Formula

If EmployeePath is a column containing 1,2,3,4:

PATHITEM(Employee[EmployeePath], 2) // Returns "2" PATHITEM(Employee[EmployeePath], 2, INTEGER) // Returns 2 as an integer

What Does It Return?

The function returns a single value:

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

When Should We Use It?

  • Analyzing or reporting specific hierarchical levels.

  • Creating drill-downs or summaries for a particular hierarchy depth.

  • Filtering rows based on a level in the hierarchy.

Examples

Basic Usage :

Retrieve the second node from a path:


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

Column Usage

Create a calculated column to display the manager (second level) for each employee:


ManagerID = PATHITEM(Employee[EmployeePath], 2, INTEGER)

Advanced Usage

Combine with conditional logic to flag specific levels:


IsSeniorManager = IF(PATHITEM(Employee[EmployeePath], 3, INTEGER) = 200, TRUE(), FALSE())

Tips and Tricks

  • Data Type Awareness: Use the [datatype] parameter to ensure consistent formatting.

  • Indexing: The position parameter is 1-based (starts from 1).

  • Delimiter Consistency: The function works with paths using commas; ensure paths are formatted correctly.

Potential Pitfalls

  • Invalid position values (e.g., negative or exceeding the path length) return errors.

  • Handling paths with inconsistent delimiters or formats can cause incorrect results.

Performance Impact of PATHITEM DAX Function:

  • Use with precomputed paths (PATH) to avoid redundant calculations.

  • Avoid complex calculations on very large datasets to maintain performance.

Related Functions You Might Need

  • PATH: Generates hierarchical paths.

  • PATHCONTAINS: Checks if a specific value exists in a path.

  • PATHLENGTH: Determines the number of levels in a path.

Want to Learn More?
For more information, check out the official Microsoft documentation for PATHITEM 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 PATHITEM function used for in Power BI?

The PATHITEM function extracts a specific node from a hierarchical path.

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

It is 1-based, meaning positions start from 1.

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

The function returns an error.

4. Can the PATHITEM function return numeric values?

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

5. Does the PATHITEM function support delimiters other than commas?

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

Previous article
Dax Function: PATHCONTAINS
Next article
Dax Function: PATHITEMREVERSE