DAX Tutorial: PATHCONTAINS Function in Power BI

The PATHCONTAINS function in Power BI DAX is used to determine whether a given path contains a specific value. It returns a Boolean value (TRUE or FALSE) based on whether the specified value is found in the path or not. This function is particularly useful when working with hierarchical data structures, such as organizational charts or product categories.

Syntax

The syntax for the PATHCONTAINS function is as follows:

PATHCONTAINS(path, value)

Where:

  • path: The path to search for the specified value.
  • value: The value to search for in the path.

Examples

Let’s take a look at some examples to better understand how the PATHCONTAINS function works.

Example 1:

Suppose we have a table called “Employees” with the following columns: EmployeeID, Name, and ManagerPath. The ManagerPath column contains the hierarchical path of each employee’s manager.

| EmployeeID | Name      | ManagerPath |
|------------|-----------|-------------|
| 1          | John      | /1/         |
| 2          | Jane      | /1/2/       |
| 3          | Mark      | /1/3/       |
| 4          | Sarah     | /1/2/4/     |
| 5          | Michael   | /1/2/5/     |

We can use the PATHCONTAINS function to determine whether a specific employee is managed by another employee. For example, to check if employee 4 is managed by employee 2, we can use the following formula:

PATHCONTAINS(Employees[ManagerPath], "/1/2/")

This formula will return TRUE, indicating that employee 4 is indeed managed by employee 2.

Example 2:

Let’s consider another example using a different data structure. Suppose we have a table called “Categories” with the following columns: CategoryID, CategoryName, and ParentCategoryID. The ParentCategoryID column contains the ID of the parent category for each category.

| CategoryID | CategoryName | ParentCategoryID |
|------------|--------------|-----------------|
| 1          | Electronics  | NULL            |
| 2          | Computers    | 1               |
| 3          | Laptops      | 2               |
| 4          | Smartphones  | 2               |
| 5          | Clothing     | NULL            |

We can use the PATHCONTAINS function to determine whether a specific category is a subcategory of another category. For example, to check if the “Laptops” category is a subcategory of the “Electronics” category, we can use the following formula:

PATHCONTAINS(Categories[ParentCategoryID], 1)

This formula will return TRUE, indicating that the “Laptops” category is indeed a subcategory of the “Electronics” category.

Conclusion

The PATHCONTAINS function in Power BI DAX provides a convenient way to search for specific values within hierarchical paths. By using this function, users can easily determine relationships between different levels of data and perform advanced calculations based on these relationships. Understanding the syntax and usage of the PATHCONTAINS function can greatly enhance the analytical capabilities of Power BI users.

Remember, the PATHCONTAINS function is just one of the many powerful functions available in Power BI DAX. Exploring and mastering these functions can unlock a whole new level of data analysis and visualization possibilities.

Leave a Comment