CONTAINSSTRING
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
Understanding the CONTAINSSTRING Function
The CONTAINSSTRING function in Power BI is used to determine whether a given string contains a specific substring or not. It returns a Boolean value of TRUE or FALSE based on whether the substring is found or not. The syntax for the CONTAINSSTRING function is as follows:
CONTAINSSTRING(❰text❱, ❰substring❱)
Where:
- `❰text❱` is the string in which the substring needs to be searched
- `❰substring❱` is the string that needs to be searched for within the text
Example Usage
Let us consider an example to better understand how the CONTAINSSTRING function works. Suppose we have a table of products with various attributes, and we want to filter the table to show only those products that contain the word "apple" in their name. We can use the following DAX formula to achieve this:
FILTER(Products, CONTAINSSTRING(Products[Name], “apple”))
Here, the FILTER function is used to filter the Products table based on the condition specified in the second argument. The CONTAINSSTRING function is used to check whether the Name column of each row in the table contains the substring "apple" or not.
Using the Function in Calculated Columns
Apart from using the CONTAINSSTRING function in filters, it can also be used in calculated columns. For example, suppose we want to add a new column to our Products table that indicates whether a product name contains the word "apple". We can use the following DAX formula:
IF(CONTAINSSTRING(Products[Name], “apple”), “Yes”, “No”)
Here, the IF function is used to return "Yes" if the product name contains the word "apple", and "No" otherwise.
Nested Functions
The CONTAINSSTRING function can also be used in nested functions to create more complex calculations. For example, suppose we have a table of sales data, and we want to calculate the total revenue generated by products that contain the word "apple" in their name. We can use the following DAX formula:
SUMX(
FILTER(
Sales,
CONTAINSSTRING(Products[Name], “apple”)
),
Sales[Revenue]
)
Here, the FILTER function is used to filter the Sales table to only include rows where the product name contains the word "apple". The SUMX function then calculates the sum of the Revenue column of the filtered table.
The CONTAINSSTRING function in Power BI is a useful tool for filtering data based on specific substrings. It can be used in various ways, including in filters, calculated columns, and nested functions. By understanding how this function works, Power BI users can create more complex and meaningful calculations to better analyze their data.
