PATHITEM Function
The PATHITEM function is used to extract a specific element from a delimited text string. It is particularly useful when working with hierarchical data structures. The function takes two arguments: the delimited text string and the position of the element to extract.
For example, consider a table with a column named “CategoryPath” containing values like “Electronics|Computers|Laptops”. To extract the second element (Computers) from this string, we can use the PATHITEM function as follows:
Category = PATHITEM('Table'[CategoryPath], 2)
This will create a new calculated column named “Category” that contains the extracted element.
PATHITEMREVERSE Function
The PATHITEMREVERSE function is similar to PATHITEM, but it extracts elements from the end of a delimited text string. It takes the same arguments as PATHITEM, but the position is counted from the end of the string.
Continuing with the previous example, if we want to extract the last element (Laptops) from the CategoryPath string, we can use the PATHITEMREVERSE function:
Subcategory = PATHITEMREVERSE('Table'[CategoryPath], 1)
This will create a new calculated column named “Subcategory” that contains the extracted element.
PATHLENGTH Function
The PATHLENGTH function is used to determine the number of elements in a delimited text string. It can be helpful when analyzing the depth or length of hierarchical paths.
Using the same CategoryPath example, if we want to calculate the depth of each category, we can use the PATHLENGTH function as follows:
CategoryDepth = PATHLENGTH('Table'[CategoryPath])
This will create a new calculated column named “CategoryDepth” that contains the number of elements in the CategoryPath string.
Conclusion
The PATHITEM, PATHITEMREVERSE, and PATHLENGTH functions in Power BI DAX provide powerful tools for working with hierarchical data structures. These functions enable us to extract specific elements, extract elements from the end of a string, and determine the length of a delimited text string. By leveraging these functions, we can enhance our data analysis and gain deeper insights into our data.
Remember to experiment with these functions and explore their capabilities to fully utilize their potential in your Power BI projects.