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’ll take a deep dive into how to use the PATH function in Power BI, including its syntax, parameters, and examples.
What is the PATH Function in Power BI?
The PATH function in Power BI is a DAX function that allows you to create a path between two nodes in a table. It works by creating a list of all the nodes that need to be traversed in order to move from one node to another.
This can be especially useful when you’re working with hierarchical data, such as organizational charts or product categories. By creating a path between nodes, you can gain insights into the relationships between different parts of your data and see how they relate to each other.
Syntax of the PATH Function
The syntax of the PATH function in Power BI is as follows:
PATH(❰Table❱, ❰Start_Column❱, ❰End_Column❱)
– `❰Table❱`: The name of the table that contains the nodes you want to create a path between.
– `❰Start_Column❱`: The name of the column that contains the starting node for the path.
– `❰End_Column❱`: The name of the column that contains the ending node for the path.
Parameters of the PATH Function
In addition to the syntax, there are several parameters you can use with the PATH function in Power BI:
– `MAXDEPTH`: This parameter specifies the maximum number of levels that can be traversed in the path. This can be useful if you want to limit the amount of data returned by the function.
– `INCLUDERELATIONSHIP`: This parameter specifies whether or not to include the relationship columns in the output. If set to TRUE, the output will include columns that show the relationship between nodes in the path.
– `ORDER`: This parameter specifies the order in which nodes are returned in the path. You can choose to return them in ascending or descending order.
Examples of the PATH Function
Let’s take a look at some examples of how to use the PATH function in Power BI.
Example 1: Creating a Simple Path
Suppose we have a table called `Employees` that contains information about our company’s employees, including their names, job titles, and managers. We want to create a path between each employee and their manager.
To do this, we can use the following formula:
PATH(Employees, Employees[Employee Name], Employees[Manager Name])
This will create a path between each employee and their manager, showing all the nodes that need to be traversed to move from one to the other.
Example 2: Adding Parameters to the PATH Function
Suppose we want to limit the depth of our path to only two levels. We can do this by adding the `MAXDEPTH` parameter to our formula:
PATH(Employees, Employees[Employee Name], Employees[Manager Name], 2)
This will create a path between each employee and their manager, but will only return nodes that are at most two levels deep.
Example 3: Including Relationship Columns
Suppose we want to include columns that show the relationship between each node in our path. We can do this by setting the `INCLUDERELATIONSHIP` parameter to TRUE:
PATH(Employees, Employees[Employee Name], Employees[Manager Name], , TRUE)
This will create a path between each employee and their manager, but will also include columns that show the relationship between each node.
The PATH function in Power BI is a powerful tool for analyzing hierarchical data. By creating paths between nodes in a table, you can gain insights into the relationships between different parts of your data and see how they relate to each other.
In this article, we’ve covered the syntax, parameters, and examples of how to use the PATH function in Power BI. With this knowledge, you can start using this powerful tool to analyze your own data and gain valuable insights into your business.