PATHCONTAINS
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
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.
