PATHCONTAINS

PATHCONTAINS

How to Use the Power BI DAX function PATHCONTAINS

One of the most powerful DAX functions in Power BI is PATHCONTAINS. This function is used to search for a substring within a given text string and returns a Boolean value of True or False. In this article, we will discuss how to use the PATHCONTAINS function in Power BI to filter data based on a specific substring.

Syntax of the PATHCONTAINS function

The syntax of the PATHCONTAINS function is as follows:


PATHCONTAINS(❰Path❱, ❰Substring❱)


The first argument of the function is the path to search within and the second argument is the substring to search for. The function returns True if the substring is found in the path, and False if it is not found.

How to use the PATHCONTAINS function in Power BI

To use the PATHCONTAINS function in Power BI, follow the steps below:

1. Open Power BI Desktop and load your data source.

2. Create a new measure by clicking on the New Measure button in the Home tab.

3. Enter the following formula in the formula bar:


MeasureName = PATHCONTAINS(❰Path❱, ❰Substring❱)


4. Replace `❰Path❱` with the path to search within and `❰Substring❱` with the substring to search for.

5. Click on Enter to create the measure.

Examples of using the PATHCONTAINS function

Let us consider some examples of using the PATHCONTAINS function in Power BI.

Example 1: Filtering data based on a substring

Suppose we have a dataset containing a list of products and their categories. We want to filter the products based on a specific category. We can accomplish this using the PATHCONTAINS function.

1. Create a new measure with the following formula:


FilterByCategory = PATHCONTAINS('Products'[Category], "Electronics")


2. This formula filters the products based on the Electronics category. The measure will return True for products belonging to the Electronics category, and False for all other products.

3. Use the measure in a visual to filter the data based on the category.

Example 2: Calculating the percentage of orders for a specific product

Suppose we have a dataset containing a list of orders and the products ordered. We want to calculate the percentage of orders for a specific product. We can accomplish this using the PATHCONTAINS function.

1. Create a new measure with the following formula:


ProductPercentage =

DIVIDE(

COUNTROWS('Orders'),

CALCULATE(

COUNTROWS('Orders'),

PATHCONTAINS('Orders'[Product], "Product A")

)

)


2. This formula calculates the percentage of orders for Product A. The measure counts the total number of orders and divides it by the number of orders that contain Product A.

3. Use the measure in a visual to display the percentage of orders for Product A.

In conclusion, the PATHCONTAINS function is a powerful DAX function in Power BI used to filter and manipulate data based on a specific substring. By following the steps outlined in this article, you can easily use the PATHCONTAINS function in your Power BI reports to create meaningful insights from your data.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)