The PATH function, used in hierarchical data environments (see example below), returns a text string with the identifiers of all parents of the current identifier, starting with the furthest.
PATH(
id_columnName,
parent_columnName
)
- id_columnName: Name of an existing column containing the unique identifiers of the rows in the table. It cannot be an expression.
- parent_columnName: Name of an existing column containing the unique identifier for the parent of the current row. It cannot be an expression.
The PATH function returns a text string.
Both the id_columnName and parent_columnName columns must contain texts or integers, and this type must be the same in both columns.
This function is used in tables in which there is some kind of internal hierarchy, and it returns a text string containing the elements related to the value contained in the current row, not limiting itself to including the elements that are one level above the element considered in the current row, but including the entire possible hierarchical chain.
The identifiers included in the text string are separated by a vertical bar ("|").
All values in parent_columnName must be included in id_columnName. That is, if a value is "parent", it is because it has at least one "child". The reverse, however, does not necessarily have to be true: there may be values in id_columnName that are not parents of other values.
If the value of parent_columnName is Blank, the PATH function returns the identifier of the child.
If id_columnName has duplicate values but they all have the same parent, the function returns the identifier of the parent common to those values. But if the duplicate values have different parents, the function returns an error.
If the value of id_columnName is Blank, the function returns Blank.
If the value of id_columnName includes a vertical bar ("|"), the function returns an error.
The result of this function also includes the identifier of the child at the end of the returned text string.
We start from the following table:
This table contains worker identifiers and the hierarchical relationship between them. The scheme of this hierarchy is shown in the following figure:
We can add a calculated column to the table showing the "path" -that is, a text string containing the identifiers of the parents- for each row using the following DAX expression:
path = PATH(Employees[EmployeeKey], Employees[ParentEmployeeKey])
In the new column it can be seen that the worker with identifier 14, for example, has as its path the one formed by worker 112 and his own identifier, or that worker 117 has workers 112, 14, 3 and 162 as parents, in this order.
