PATHLENGTH DAX Function (Parent-child)
Returns 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
| Parameter | Attributes | Description |
|---|---|---|
| Path |
A string which contains a delimited list of IDs. |
Return values
The number of items that are parents to the specified item in a given PATH result, including the specified item.
» 4 related functions
Examples
-- PATH generates a column containing the path of each node in a
-- parent/child hierarchy
--
-- PATHLENGTH returns the length of a path column
-- PATHCONTAINS checks if a path column contains a value
DEFINE
COLUMN Account[AccountPath] = PATH ( Account[AccountKey], Account[ParentAccountKey] )
COLUMN Account[PathLength] = PATHLENGTH ( Account[AccountPath] )
COLUMN Account[PathContains2] = PATHCONTAINS ( Account[AccountPath], 2 )
EVALUATE
Account
ORDER BY [AccountPath]
| AccountKey | ParentAccountKey | AccountName | AccountPath | PathLength | PathContains2 |
|---|---|---|---|---|---|
| 1 | (Blank) | Profit and Loss after tax | 1 | 1 | false |
| 19 | 1 | Taxation | 1|19 | 2 | false |
| 24 | 1 | Profit and Loss before tax | 1|24 | 2 | false |
| 2 | 24 | Income | 1|24|2 | 3 | true |
| 4 | 2 | Sale Revenue | 1|24|2|4 | 4 | true |
| 3 | 24 | Expense | 1|24|3 | 3 | false |
| 5 | 3 | Cost of Goods Sold | 1|24|3|5 | 4 | false |
| 6 | 3 | Selling, General & Administrative Expenses | 1|24|3|6 | 4 | false |
| 10 | 6 | Light, Heat, Communication Cost | 1|24|3|6|10 | 5 | false |
| 11 | 6 | Property Costs | 1|24|3|6|11 | 5 | false |
| 12 | 6 | Other Expenses | 1|24|3|6|12 | 5 | false |
| 13 | 6 | Marketing Cost | 1|24|3|6|13 | 5 | false |
| 14 | 13 | Holiday Ad Cost | 1|24|3|6|13|14 | 6 | false |
| 20 | 14 | Radio & TV | 1|24|3|6|13|14|20 | 7 | false |
| 21 | 14 | 1|24|3|6|13|14|21 | 7 | false | |
| 22 | 14 | Internet | 1|24|3|6|13|14|22 | 7 | false |
| 23 | 14 | Other | 1|24|3|6|13|14|23 | 7 | false |
| 15 | 13 | Spring Ad Cost | 1|24|3|6|13|15 | 6 | false |
| 16 | 13 | Back-to-School Ad Cost | 1|24|3|6|13|16 | 6 | false |
| 17 | 13 | Business Ad Cost | 1|24|3|6|13|17 | 6 | false |
| 18 | 13 | Tax Time / Summer Ad Cost | 1|24|3|6|13|18 | 6 | false |
| 7 | 6 | Administration Expense | 1|24|3|6|7 | 5 | false |
| 8 | 6 | IT Cost | 1|24|3|6|8 | 5 | false |
| 9 | 6 | Human Capital | 1|24|3|6|9 | 5 | false |
Related articles
Learn more about PATHLENGTH in the following articles:
-
Parent-Child Hierarchies
DAX does not directly support parent-child hierarchies. To obtain a browsable hierarchy in the data model, you have to naturalize a parent-child hierarchy. DAX provides specific functions to naturalize a parent-child hierarchy using calculated columns. The complete pattern also includes measures that improve the visualization of ragged hierarchies in Power Pivot. » Read more
Related functions
Other related functions are:
Last update: Sep 12, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/pathlength-function-dax