PATHLENGTH
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
In this article, we will explore the basics of the PATHLENGTH function, how to use it, and some practical examples to help you get started.
Understanding the PATHLENGTH Function
The PATHLENGTH function allows you to retrieve the length of a path in a hierarchy. It takes a column as an argument, which represents the path to a leaf node in the hierarchy.
The function starts at the root node and counts the number of nodes present in the path to the leaf node. The result is the length of the path.
Syntax of the PATHLENGTH Function
The syntax of the PATHLENGTH function is as follows:
PATHLENGTH(❰column❱)
Where `❰column❱` is the name of the column that contains the path to a leaf node in the hierarchy.
Example of Using the PATHLENGTH Function
Let’s consider an example to understand how the PATHLENGTH function works.
Suppose you have a table named “Employees” that contains the following data:
| Employee ID | Employee Name | Manager ID |
|————-|—————|————|
| 1 | John Smith | 2 |
| 2 | Jane Doe | 3 |
| 3 | Mark Johnson | 4 |
| 4 | Mary Williams| NULL |
This table represents a hierarchical structure where each employee is managed by another employee, except for the top-level manager who does not have a manager.
To use the PATHLENGTH function, we need to create a calculated column that represents the path to the top-level manager for each employee. We can do this using the following DAX formula:
Path = PATH("Employees"[Manager ID], "Employees"[Employee ID])
This formula creates a new column named “Path” that contains the path from each employee to the top-level manager.
Now we can use the PATHLENGTH function to retrieve the length of the path for each employee. We can do this using the following DAX formula:
Path Length = PATHLENGTH("Employees"[Path])
This formula creates a new column named “Path Length” that contains the length of the path for each employee.
Practical Applications of the PATHLENGTH Function
Now that we understand how the PATHLENGTH function works, let’s explore some practical applications of this function.
Analyzing the Depth of a Hierarchy
The PATHLENGTH function can be used to analyze the depth of a hierarchy. The depth of a hierarchy is the number of levels present in the hierarchy.
For example, if we have a hierarchy with three levels, the depth of the hierarchy is three.
To use the PATHLENGTH function to analyze the depth of a hierarchy, we can retrieve the maximum path length for all the leaf nodes in the hierarchy. The maximum path length represents the depth of the hierarchy.
We can do this using the following DAX formula:
Max Path Length = MAXX(VALUES("Employees"[Path Length]), "Employees"[Path Length])
This formula retrieves the maximum path length for all the leaf nodes in the hierarchy and returns the result as a single value.
Identifying Longest Paths in a Hierarchy
The PATHLENGTH function can also be used to identify the longest paths in a hierarchy. The longest paths represent the most complex paths in the hierarchy.
To use the PATHLENGTH function to identify the longest paths in a hierarchy, we can sort the leaf nodes in descending order based on their path length.
We can do this using the following DAX formula:
Rank = RANKX(ALL("Employees"), "Employees"[Path Length], , DESC, Dense)
This formula creates a new column named “Rank” that assigns a rank to each leaf node based on their path length. The leaf node with the longest path will have a rank of 1.
We can then use the FILTER function to retrieve the leaf nodes with the highest rank, which represents the longest paths in the hierarchy.
Top 5 Longest Paths = FILTER("Employees", "Employees"[Rank] ❰= 5)
This formula retrieves the top 5 leaf nodes with the highest rank, which represent the 5 longest paths in the hierarchy.
Grouping Data Based on Path Length
The PATHLENGTH function can also be used to group data based on their path length. This can be useful when you want to analyze the data based on the depth of the hierarchy.
To group the data based on path length, we can create a new column that represents the path length category for each leaf node. We can do this using the following DAX formula:
Path Length Category = SWITCH("Employees"[Path Length],
0, "No Path",
1, "Level 1",
2, "Level 2",
3, "Level 3",
"Other")
This formula creates a new column named “Path Length Category” that categorizes the leaf nodes based on their path length.
We can then use the GROUPBY function to group the data based on the path length category and retrieve the count of leaf nodes in each category.
Group by Path Length Category = GROUPBY("Employees", "Employees"[Path Length Category],
"Count", COUNTX(CURRENTGROUP(), [Employee ID]))
This formula groups the data based on the path length category and retrieves the count of leaf nodes in each category.
The PATHLENGTH function is a powerful tool that can be used to retrieve the length of a path in a hierarchy. It can help you analyze complex hierarchical data and gain insights into the depth and complexity of the hierarchy.
In this article, we explored the basics of the PATHLENGTH function, how to use it, and some practical examples to help you get started. We hope that this article has helped you understand the power of the PATHLENGTH function and how it can be used to analyze hierarchical data in Power BI.
