Dax Function: PATHCONTAINS
Category: Parent and Child Functions
The PATHCONTAINS function in Power BI is a DAX function used to determine whether a specific value exists within a delimited path generated by the PATH function.
Purpose
The primary purpose of the PATHCONTAINS function is to enable logical checks within hierarchical paths. It helps verify if a specific node (value) is part of a hierarchy, facilitating analysis and filtering.
Type of Calculations it Performs
Performs logical checks within hierarchical paths.
Returns a boolean value (
TRUEorFALSE) indicating the presence of a value.
Practical Use Cases
Filtering rows based on hierarchical membership.
Identifying nodes or entities that belong to a specific parent or ancestor.
Supporting security models where access is based on hierarchical relationships.
PATHCONTAINS(<path>, <value>)</value></path>
| Parameter | Type | Description |
|---|---|---|
path | Column | A column or calculated value containing the hierarchical path string. |
value | Scalar | A scalar value to check for existence within the path. |
How Does PATHCONTAINS Dax Works
The PATHCONTAINS function parses the delimited string in the path and checks if the specified value matches any of the items. The match is case-insensitive and checks exact equality.
Example Formula
If EmployeePath is a path column created using PATH, you can check if 102 exists in the path:
PATHCONTAINS(Employee[EmployeePath], "102")What Does It Return?
The function returns a boolean (TRUE or FALSE):
TRUE: Thevalueexists within thepath.FALSE: Thevaluedoes not exist in thepath.
When Should We Use It?
Use in scenarios requiring hierarchical filtering, such as:
Checking if a specific employee reports to a manager.
Verifying membership in a specific product category.
Implementing row-level security based on hierarchy.
Examples
Basic Usage :
Check if a node exists in a single path:
PATHCONTAINS("1,2,3,4", "3") // Returns TRUE
Column Usage
Create a calculated column to identify rows where the value is in the path:
IsPartOfPath = PATHCONTAINS(Employee[EmployeePath], "102")
Advanced Usage
Combine with a filter to retrieve rows where a node exists:
FILTER(Employee, PATHCONTAINS(Employee[EmployeePath], "102"))
Tips and Tricks
Case-Insensitive Matching: Ensure consistency in the values to avoid mismatches due to formatting.
Delimiter Awareness: The default delimiter is a comma; ensure paths are correctly formatted.
Optimize Hierarchical Checks: Use indexed values or keys in paths for efficient processing.
Potential Pitfalls
Inconsistent formatting of
valueorpathcan lead to incorrect results.Large datasets with deeply nested hierarchies may slow down performance.
Performance Impact of PATHCONTAINS DAX Function:
Ensure paths are pre-computed using the
PATHfunction to avoid recalculating them dynamically.Use with filtered datasets to improve efficiency.
Related Functions You Might Need
PATH: Generates a hierarchical path.
PATHITEM: Extracts a specific node from a path.
PATHLENGTH: Counts the number of levels in a path.
Want to Learn More?
For more information, check out the official Microsoft documentation for PATHCONTAINS You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
It checks whether a specific value exists within a hierarchical path.
No, the function is case-insensitive.
The function returns FALSE.
It works with paths created by PATH, which use commas as delimiters by default.
It can be if paths are not pre-computed. Optimize by calculating paths once.