CONTAINSSTRING DAX Function (Information)
Returns TRUE if one text string contains another text string. CONTAINSSTRING is not case-sensitive, but it is accent-sensitive.
Syntax
CONTAINSSTRING ( <WithinText>, <FindText> )
| Parameter | Attributes | Description |
|---|---|---|
| WithinText |
The text in which you want to search for FindText. |
|
| FindText |
The text you want to find. You can use the ? and * wildcard characters; use ~? and ~* to find the ? and * characters. |
Return values
Scalar A single boolean value.
A value of TRUE if the string WithinText contains the string FindText – the comparison is not case-sensitive.
Remarks
CONTAINSSTRING internally uses the SEARCH function.
» 1 related function
Examples
-- CONTAINSSTRING is syntax sugar for SEARCH
-- The following two queries return the same result
EVALUATE
FILTER (
ALL ( 'Product'[Color] ),
CONTAINSSTRING ( 'Product'[Color], "r?y" )
)
EVALUATE
FILTER (
ALL ( 'Product'[Color] ),
SEARCH ( "r?y", 'Product'[Color], 1, 0 ) > 0
)
| Color |
|---|
| Grey |
| Silver Grey |
| Color |
|---|
| Grey |
| Silver Grey |
-- CONTAINSSTRING checks whether a string contains another string, using
-- case-insensitive match. Therefore, "RED" = "red".
-- CONTAINSSTRINGEXACT uses case-sensitive matching
DEFINE
MEASURE Sales[RED Prods (case insensitive)] =
COUNTROWS (
FILTER ( Product, CONTAINSSTRING ( 'Product'[Product Name], "red" ) )
)
MEASURE Sales[RED Prods (case sensitive)] =
COUNTROWS (
FILTER ( Product, CONTAINSSTRINGEXACT ( 'Product'[Product Name], "red" ) )
)
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
Product[Product Name],
"RED Prods (case sensitive)", [RED Prods (case sensitive)],
"RED Prods (case insensitive)", [RED Prods (case insensitive)]
),
OR (
[RED Prods (case insensitive)] <> [RED Prods (case sensitive)],
[RED Prods (case sensitive)] = 1
)
)
ORDER BY
[RED Prods (case sensitive)] DESC
| Product Name | RED Prods (case sensitive) | RED Prods (case insensitive) |
|---|---|---|
| Proseware 23ppm Laser Printer with Wireless and Wired Network Interfaces M680 White | 1 | 1 |
| The Phone Company Touch Screen Phones Infrared M901 Black | 1 | 1 |
| The Phone Company Touch Screen Phones Infrared M901 Grey | 1 | 1 |
| Proseware 23ppm Laser Printer with Wireless and Wired Network Interfaces M680 Black | 1 | 1 |
| The Phone Company Touch Screen Phones Infrared M901 Gold | 1 | 1 |
| Contoso Touch Screen Phones Infrared M901 Black | 1 | 1 |
| Proseware 23ppm Laser Printer with Wireless and Wired Network Interfaces M680 Grey | 1 | 1 |
| NT Bluetooth Active Headphones E202 Red | (Blank) | 1 |
| NT Wireless Bluetooth Stereo Headphones M402 Red | (Blank) | 1 |
| NT Wireless Transmitter and Bluetooth Headphones M150 Red | (Blank) | 1 |
| WWI Wireless Transmitter and Bluetooth Headphones X250 Red | (Blank) | 1 |
| Fabrikam Laptop14.1W M4180 Red | (Blank) | 1 |
| Fabrikam Laptop14.1 E4101 Red | (Blank) | 1 |
| Fabrikam Laptop8.9 E8002 Red | (Blank) | 1 |
| Fabrikam Laptop12 M2002 Red | (Blank) | 1 |
| Fabrikam Laptop13.3 M3000 Red | (Blank) | 1 |
| Fabrikam Laptop13.3W M3080 Red | (Blank) | 1 |
| Adventure Works Laptop19W X1980 Red | (Blank) | 1 |
| Adventure Works Laptop15 M1501 Red | (Blank) | 1 |
| Adventure Works Laptop12 M1201 Red | (Blank) | 1 |
| Adventure Works Laptop16 M1601 Red | (Blank) | 1 |
| Adventure Works Laptop15.4W M1548 Red | (Blank) | 1 |
| Adventure Works Laptop8.9 E0890 Red | (Blank) | 1 |
| WWI Desktop PC1.60 E1600 Red | (Blank) | 1 |
| SV 40GB USB2.0 Portable Hard Disk E400 Red | (Blank) | 1 |
| SV 80GB USB2.0 Portable Hard Disk E500 Red | (Blank) | 1 |
| Contoso Mini Battery Charger Kit E320 Red | (Blank) | 1 |
| SV DVD 48 DVD Storage Binder M50 Red | (Blank) | 1 |
| SV DVD 58 DVD Storage Binder M55 Red | (Blank) | 1 |
| SV DVD 38 DVD Storage Binder E25 Red | (Blank) | 1 |
| SV DVD 60 DVD Storage Binder L20 Red | (Blank) | 1 |
| SV DVD 55DVD Storage Binder M56 Red | (Blank) | 1 |
| Contoso DVD 48 DVD Storage Binder M50 Red | (Blank) | 1 |
| Contoso DVD 58 DVD Storage Binder M55 Red | (Blank) | 1 |
| Contoso DVD 38 DVD Storage Binder E25 Red | (Blank) | 1 |
| Contoso DVD 60 DVD Storage Binder L20 Red | (Blank) | 1 |
| Contoso DVD 55DVD Storage Binder M56 Red | (Blank) | 1 |
| MGS Hand Games for kids E300 Red | (Blank) | 1 |
| MGS Hand Games for students E400 Red | (Blank) | 1 |
| MGS Hand Games men M300 Red | (Blank) | 1 |
| MGS Hand Games women M400 Red | (Blank) | 1 |
| MGS Hand Games for 12-16 boys E600 Red | (Blank) | 1 |
| MGS Hand Games for Office worker L299 Red | (Blank) | 1 |
| SV Hand Games for kids E30 Red | (Blank) | 1 |
| SV Hand Games for students E40 Red | (Blank) | 1 |
| SV Hand Games men M30 Red | (Blank) | 1 |
| SV Hand Games women M40 Red | (Blank) | 1 |
| SV Hand Games for Office worker L28 Red | (Blank) | 1 |
| Contoso Washer & Dryer 27in L270 Red | (Blank) | 1 |
| Contoso Washer & Dryer 25.5in M255 Red | (Blank) | 1 |
| Contoso Washer & Dryer 24in M240 Red | (Blank) | 1 |
| Contoso Washer & Dryer 21in E210 Red | (Blank) | 1 |
| Contoso Washer & Dryer 15.5in E155 Red | (Blank) | 1 |
| Litware Refrigerator 19CuFt M760 Red | (Blank) | 1 |
| Litware Refrigerator 9.7CuFt M560 Red | (Blank) | 1 |
| Fabrikam Microwave 1.5CuFt X1100 Red | (Blank) | 1 |
| Fabrikam Microwave 2.2CuFt M1250 Red | (Blank) | 1 |
| Fabrikam Microwave 1.6CuFt M1250 Red | (Blank) | 1 |
| Fabrikam Microwave 1.0CuFt E1100 Red | (Blank) | 1 |
| Fabrikam Microwave 0.9CuFt E0900 Red | (Blank) | 1 |
| Fabrikam Microwave 0.8CuFt E0800 Red | (Blank) | 1 |
| Litware Microwave 1.5CuFt X110 Red | (Blank) | 1 |
| Litware Microwave 2.2CuFt M125 Red | (Blank) | 1 |
| Litware Microwave 1.6CuFt M125 Red | (Blank) | 1 |
| Litware Microwave 1.0CuFt E110 Red | (Blank) | 1 |
| Litware Microwave 0.9CuFt E090 Red | (Blank) | 1 |
| Litware Microwave 0.8CuFt E080 Red | (Blank) | 1 |
| Contoso Microwave 1.5CuFt X0110 Red | (Blank) | 1 |
| Contoso Microwave 2.2CuFt M0125 Red | (Blank) | 1 |
| Contoso Microwave 1.6CuFt M0125 Red | (Blank) | 1 |
| Contoso Microwave 1.0CuFt E0110 Red | (Blank) | 1 |
| Contoso Microwave 0.9CuFt E0090 Red | (Blank) | 1 |
| Contoso Microwave 0.8CuFt E0080 Red | (Blank) | 1 |
| Contoso Water Heater 7.2GPM X1800 Red | (Blank) | 1 |
| Contoso Water Heater 4.3GPM M1250 Red | (Blank) | 1 |
| Contoso Water Heater 4.0GPM M1250 Red | (Blank) | 1 |
| Contoso Water Heater 2.6GPM E0900 Red | (Blank) | 1 |
| Contoso Water Heater 1.5GPM E0800 Red | (Blank) | 1 |
| Contoso Air conditioner 25000BTU L1672 Red | (Blank) | 1 |
| Contoso Air conditioner 12000BTU M0640 Red | (Blank) | 1 |
| Contoso Air conditioner 10000BTU M0490 Red | (Blank) | 1 |
| Contoso Air conditioner 8000BTU M0320 Red | (Blank) | 1 |
| Contoso Air conditioner 7000BTU E0260 Red | (Blank) | 1 |
| Contoso Air conditioner 6000BTU E0180 Red | (Blank) | 1 |
| Contoso Air conditioner 5200BTU E0100 Red | (Blank) | 1 |
| Proseware Air conditioner 25000BTU L167 Red | (Blank) | 1 |
| Proseware Air conditioner 12000BTU M640 Red | (Blank) | 1 |
| Proseware Air conditioner 10000BTU M490 Red | (Blank) | 1 |
| Proseware Air conditioner 8000BTU M320 Red | (Blank) | 1 |
| Proseware Air conditioner 7000BTU E260 Red | (Blank) | 1 |
| Proseware Air conditioner 6000BTU E180 Red | (Blank) | 1 |
| Proseware Air conditioner 5200BTU E100 Red | (Blank) | 1 |
| Litware USB Foam Fan E1301 Red | (Blank) | 1 |
| Litware 180 CFM Vertical Discharge Fan X450 Red | (Blank) | 1 |
| Litware USB Durable Desk Soft Fan E1401 Red | (Blank) | 1 |
| Litware 80mm LED Dual PCI Slot Fan E1501 Red | (Blank) | 1 |
| Cigarette Lighter Adapter for Contoso Phones E110 Red | (Blank) | 1 |
| Contoso Touch Stylus Pen E150 Red | (Blank) | 1 |
| Contoso Bluetooth Active Headphones L15 Red | (Blank) | 1 |
| SV Hand Games for 12-16 boys E60 Red | (Blank) | 1 |
| Contoso 2G MP3 Player E200 Red | (Blank) | 1 |
| Contoso 8GB Super-Slim MP3/Video Player M800 Red | (Blank) | 1 |
| Contoso 16GB Mp5 Player M1600 Red | (Blank) | 1 |
| Contoso 32GB Video MP3 Player M3200 Red | (Blank) | 1 |
| WWI 4GB Video Recording Pen X200 Red | (Blank) | 1 |
| WWI 1GB Digital Voice Recorder Pen E100 Red | (Blank) | 1 |
Related articles
Learn more about CONTAINSSTRING in the following articles:
-
From SQL to DAX: String Comparison
In DAX string comparison requires you more attention than in SQL, for several reasons: DAX doesn’t offer the same set of features you have in SQL, a few text comparison functions in DAX are only case-sensitive and others only case-insensitive,… » Read more
-
Reading active Power BI security roles in DAX
This article describes how to read the active security roles in a Tabular model for Power BI or Analysis Services. This way, you can use measures and calculation groups to customize a report based dynamically on security roles active for the current user. » Read more
-
Optimizing text search in DAX
This article describes how to optimize a text search operation in DAX. This technique can improve the performance of Power BI reports that use the contains condition in the filter pane or the filter mode of the Smart Filter Pro custom visual. » Read more
Related functions
Other related functions are:
Last update: Oct 27, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Imke Feldmann
Microsoft documentation: https://docs.microsoft.com/en-us/dax/containsstring-function-dax
2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us » Privacy Policy & Cookies