PATHITEM
The key at a position in a PATH text, counted from the top.
Group: Parent and child. Compatible with DAX.
Syntax
PATHITEM ( <path>, <position> [, <type>] )| Parameter | Type | Description |
|---|---|---|
path | Text | A path as PATH writes it: keys separated by |. |
position | Whole number | Which key, counted from the top: 1 is the top of the tree. |
type | Keyword optional | TEXT (or 0) to get the key as text, which is what you get when it is left out; INTEGER (or 1) to get a whole number. Any other value is an error. |
Returns
Text or whole number. The key at that position, or BLANK when the path has no key there.
Remarks
- A position past the end of the path, 0 or a negative one gives BLANK, not an error, so a column per level simply stays blank below the rows that are not that deep.
- A position with a fraction is rounded, a half away from zero.
- With
INTEGER, a key with decimals is rounded to a whole number, a half to the even one (2.5 gives 2, 3.5 gives 4); a key that is no number, empty text included, is an error. - Ask for
INTEGERwhen the key is looked up in a column of whole numbers, asLOOKUPVALUEdoes in example 2.
Examples
The keys of a path, from the top
EVALUATE
ROW (
"1st", PATHITEM ( "1|20|21", 1 ),
"2nd", PATHITEM ( "1|20|21", 2, INTEGER ),
"3rd", PATHITEM ( "1|20|21", 3 ),
"4th", PATHITEM ( "1|20|21", 4 )
)| 1st | 2nd | 3rd | 4th |
|---|---|---|---|
| 1 | 20 | 21 | (blank) |
The tree as levels
A column for each level, the name looked up from the key: the usual way to show a parent-child tree in a matrix.
EVALUATE
VAR Levels =
ADDCOLUMNS ( Companies, "Path", PATH ( Companies[Key], Companies[Parent] ) )
RETURN
SELECTCOLUMNS (
Levels,
"Company", Companies[Company],
"Level 1", LOOKUPVALUE ( Companies[Company], Companies[Key], PATHITEM ( [Path], 1, INTEGER ) ),
"Level 2", LOOKUPVALUE ( Companies[Company], Companies[Key], PATHITEM ( [Path], 2, INTEGER ) ),
"Level 3", LOOKUPVALUE ( Companies[Company], Companies[Key], PATHITEM ( [Path], 3, INTEGER ) )
)
ORDER BY [Level 1], [Level 2], [Level 3]| Company | Level 1 | Level 2 | Level 3 |
|---|---|---|---|
| Example Brewery Group | Example Brewery Group | (blank) | (blank) |
| Brewing | Example Brewery Group | Brewing | (blank) |
| Bergen plant | Example Brewery Group | Brewing | Bergen plant |
| Oslo plant | Example Brewery Group | Brewing | Oslo plant |
| Trondheim plant | Example Brewery Group | Brewing | Trondheim plant |
| Distribution | Example Brewery Group | Distribution | (blank) |
| Alnabru depot | Example Brewery Group | Distribution | Alnabru depot |
| Example Brewery Holding | Example Brewery Group | Example Brewery Holding | (blank) |
A type that is neither TEXT nor INTEGER
EVALUATE
ROW ( "Type 2", PATHITEM ( "1|20|21", 1, 2 ) )Result: an error.