PATH
The keys from the top of a parent-child hierarchy down to the current row, joined with |.
Group: Parent and child. Compatible with DAX.
Syntax
PATH ( <ID_columnName>, <parent_columnName> )| Parameter | Type | Description |
|---|---|---|
ID_columnName | Column | The column of keys: each row's own key. |
parent_columnName | Column | The column with the key of each row's parent; blank for a row at the top. |
Returns
Text. The keys from the top of the tree down to the current row, separated by |.
Remarks
- Both arguments are columns of one table of the model: its key column and its parent column. A column of a table made in the query, with
DATATABLEor in a variable, is an error. - It works on one row of that table at a time: in a calculated column, or in an iterator over the table such as
ADDCOLUMNSorFILTER. Anywhere else it is an error. - A row whose parent is blank is at the top, and its path is just its own key.
- Parents are looked up in the whole table, whatever filters are on it: in a table filtered to one company, that company's path still runs up to the top (example 3).
- It is usually written once, as a calculated column, and read with
PATHLENGTH,PATHITEM,PATHITEMREVERSEandPATHCONTAINS. - Keys are matched without regard to case, as OAX compares text: a parent written a finds the row whose key is A.
- A parent that is no row's key is taken as the top: the path begins with it, as written, so a row d whose parent x is no key has the path x|d.
Compared with DAX
Power BI refuses a parent that is no row's key: the query fails, and a model with such a calculated column does not load. OpenBI answers, beginning the path with that parent, and when a calculated column's PATH meets one as the model loads, it warns, naming the column and its first such rows. On OpenBI Server the warning is in the refresh's history. Fix the data to have the same answer in both.
Examples
The path of each company
EVALUATE
ADDCOLUMNS ( Companies, "Path", PATH ( Companies[Key], Companies[Parent] ) )
ORDER BY Companies[Key]| Companies[Key] | Companies[Parent] | Companies[Company] | Path |
|---|---|---|---|
| 1 | (blank) | Example Brewery Group | 1 |
| 10 | 1 | Brewing | 1|10 |
| 11 | 10 | Bergen plant | 1|10|11 |
| 12 | 10 | Oslo plant | 1|10|12 |
| 13 | 10 | Trondheim plant | 1|10|13 |
| 20 | 1 | Distribution | 1|20 |
| 21 | 20 | Alnabru depot | 1|20|21 |
| 30 | 1 | Example Brewery Holding | 1|30 |
The financial accounts and their way up
Only the accounts of one section are listed; their paths still begin at the top of the chart of accounts.
EVALUATE
SELECTCOLUMNS (
FILTER ( Accounts, Accounts[Section] = "Financial" ),
"Account", Accounts[Account],
"Path", PATH ( Accounts[Key], Accounts[Parent] )
)
ORDER BY [Path]| Account | Path |
|---|---|
| Net financial items | 1|2|80 |
| Interest income | 1|2|80|8050 |
| Currency gains and losses | 1|2|80|8060 |
| Interest expense | 1|2|80|8150 |
Filters do not cut the path
EVALUATE
CALCULATETABLE (
ADDCOLUMNS ( Companies, "Path", PATH ( Companies[Key], Companies[Parent] ) ),
Companies[Key] = 21
)| Companies[Key] | Companies[Parent] | Companies[Company] | Path |
|---|---|---|---|
| 21 | 20 | Alnabru depot | 1|20|21 |
Related functions
PATHLENGTH, PATHITEM, PATHITEMREVERSE, PATHCONTAINS, LOOKUPVALUE.