Introduction
Power BI supports some functions which show a Parent-Child hierarchy. In this article, you will learn about such functions. These functions are very useful to manage tables or columns that have a hierarchy in the data.
Let’s have a look and see how these functions work.
Functions and their Descriptions
PATH
Returns a string that contains a delimited list of IDs, starting with the top/root of a hierarchy and ending with the specified ID.
Syntax
PATH(ID_ColumnName, Parent_ColumnName)
ID_ColumnName
The name of an existing column containing the unique identifier for rows in the table. This cannot be an expression. The data type of the value in ID_ColumnName must be text or integer, and must also be the same data type as the column referenced in Parent_ColumnName.
Parent_ColumnName
The name of an existing column containing the unique identifier for the parent of the current row. This cannot be an expression. The data type of the value in the Parent_ColumnName data type must be text or integer and must be the same data type as the value in ID_columnName.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
PATHCONTAINS
Returns TRUE if the specified item exists within the specified path.
Syntax
PATHCONTAINS (Path, Item)
Path
A string created as the result of evaluating a PATH function.
Item
A text expression to look for in the path result.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
PATHITEM
Returns the nth item in the delimited list produced by the path function.
Syntax
PATHITEM (Path, Position, [Type])
Path
A text string in the form of the results of a PATH function.
Position
An integer expression with the position of the item to be returned.
Type
It is Optional. An enumeration that defines the data type of the result. Enumeration either can be “TEXT” and alternate enumeration will be “0” means results are
returned with the data type text. (default) Or It can be an “INTEGER” and the alternate value will be “1”, which means results will be returned as an integer.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
PATHITEMREVERSE
Returns the nth item in the delimited list produced by the Path function, counting backward from the last item in the path.
Syntax
PATHITEMREVERSE (Path, Position, [Type])
Path
A text string resulting from the evaluation of a PATH function.
Position
An integer expression with the position of the item to be returned. The position is counted backward from right to left.
Type
It is Optional. An enumeration that defines the data type of the result. Enumeration either can be “TEXT” and alternate enumeration will be “0” means results are returned with the data type text. (default) Or It can be an “INTEGER” and the alternate value will be “1” means results will be returned as an integer.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
PATHLENTH
Returns the number of items in a particular path string. This function returns 1 for the path generated for an ID at the Top/root of a hierarchy.
Syntax
PATHLENTH(Path)
Path
A text expression resulting from the evaluation of a PATH function.
Note
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Summary
These are some useful parent and child functions that are commonly used. I hope you understood these functions. Thanks for reading, and have a nice day!