CONTAINS
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 useful DAX functions is CONTAINS. This function is used to check whether a specified text string is found within another text string. This function can be used in a variety of scenarios, such as filtering data based on certain criteria or checking the existence of specific text in a column. In this article, we will explore how to use the CONTAINS function in Power BI.
Syntax of the CONTAINS function
Before we dive into the usage of the CONTAINS function, let’s first examine its syntax. The basic syntax of the CONTAINS function is as follows:
CONTAINS(❰text❱, ❰substring❱)
Here, `❰text❱` is the text string that you want to search within, and `❰substring❱` is the text string that you want to search for. The function returns a boolean value (TRUE or FALSE) based on whether the substring is found within the text.
Examples of using the CONTAINS function
Let's take a look at some examples of using the CONTAINS function in Power BI.
Example 1: Using CONTAINS to filter data
Suppose you have a table of customer data that includes a column for their email addresses. You want to filter the data to show only customers whose email addresses contain the string "gmail.com". Here's how you can use the CONTAINS function to achieve this:
FILTER(‘Customer’, CONTAINS(Customer[Email], “gmail.com”))
In this example, `'Customer'` refers to the table that contains the customer data, `Customer[Email]` refers to the column that contains the email addresses, and `"gmail.com"` is the substring that we want to search for. The FILTER function is used to filter the data based on the result of the CONTAINS function.
Example 2: Using CONTAINS to calculate a measure
Suppose you have a table of sales data that includes a column for the product name and a column for the sales amount. You want to calculate the total sales for all products that contain the string "widget" in their name. Here's how you can use the CONTAINS function to achieve this:
Total Widget Sales = SUMX(FILTER(‘Sales Data’, CONTAINS(‘Sales Data'[Product Name], “widget”)), ‘Sales Data'[Sales Amount])
In this example, `'Sales Data'` refers to the table that contains the sales data, `Sales Data'[Product Name]` refers to the column that contains the product names, and `"widget"` is the substring that we want to search for. The FILTER function is used to filter the data based on the result of the CONTAINS function. The SUMX function is used to calculate the total sales amount for all products that meet the filter criteria.
The CONTAINS function is a powerful tool for data analysis in Power BI. By using this function, you can easily search for specific text strings within your data, filter your data based on certain criteria, and calculate measures based on specific text strings. By mastering the usage of the CONTAINS function, you can take your data analysis skills to the next level and unlock a whole new world of possibilities.
