A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is the PATHITEM Function?
The PATHITEM function is a DAX function that extracts a specific item from a delimited text string. This function is particularly useful when working with hierarchical data, such as organizational charts or product categories. The function allows users to extract a specific level or node from a path, which can be used to create calculated measures or to filter data.
The PATHITEM function takes two arguments: the text string and the index number of the item to extract. The text string is the hierarchical path, which consists of a series of nodes separated by a delimiter. The index number specifies which node to extract, with 1 being the first node, 2 being the second node, and so on.
Example
Suppose we have a table of products, with each product belonging to a specific category. The categories are organized in a hierarchical structure, with a delimiter of ” ❱ ” between each level. The following table shows a sample of the data:
| Product Name | Category |
|————–|———-|
| Product A | Category A ❱ Subcategory A1 ❱ Sub-subcategory A1.1 |
| Product B | Category B ❱ Subcategory B1 ❱ Sub-subcategory B1.1 |
| Product C | Category B ❱ Subcategory B1 ❱ Sub-subcategory B1.2 |
To extract the first level of the category hierarchy, we can use the following DAX expression:
=PATHITEM([Category],1," ❱ ")
This expression returns the following values:
| Product Name | Category | Level 1 |
|————–|———-|———|
| Product A | Category A ❱ Subcategory A1 ❱ Sub-subcategory A1.1 | Category A |
| Product B | Category B ❱ Subcategory B1 ❱ Sub-subcategory B1.1 | Category B |
| Product C | Category B ❱ Subcategory B1 ❱ Sub-subcategory B1.2 | Category B |
We can also extract the second level of the category hierarchy using the following expression:
=PATHITEM([Category],2," ❱ ")
This expression returns the following values:
| Product Name | Category | Level 2 |
|————–|———-|———|
| Product A | Category A ❱ Subcategory A1 ❱ Sub-subcategory A1.1 | Subcategory A1 |
| Product B | Category B ❱ Subcategory B1 ❱ Sub-subcategory B1.1 | Subcategory B1 |
| Product C | Category B ❱ Subcategory B1 ❱ Sub-subcategory B1.2 | Subcategory B1 |
We can use these extracted values to create calculated measures or to filter data based on specific levels of the hierarchy.
The PATHITEM function is a powerful tool for working with hierarchical data in Power BI. By extracting specific nodes from a delimited text string, users can create calculated measures and filters based on specific levels of the hierarchy. This function is particularly useful when working with organizational charts or product categories, where data is often organized in a hierarchical structure. With the PATHITEM function, users can easily analyze and visualize their data in Power BI.