SEARCH
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
What is the SEARCH function?
The SEARCH function is used to search for a specified string within another string. It returns the position of the first character of the specified string within the other string. If the specified string is not found, it returns a value of 0.
The syntax for the SEARCH function is as follows:
SEARCH(find_text, within_text, [start_num])
The arguments for the function are:
- `find_text`: This is the string that you want to search for within another string.
- `within_text`: This is the string that you want to search within.
- `start_num`: (Optional) This is the position within the string where you want to start the search. If you omit this argument, the search will begin at the first character.
Using the SEARCH function in Power BI
Now that we understand the basics of the SEARCH function, let's take a look at how to use it in Power BI.
Example 1
Suppose we have a table with a column called `Product Name`, and we want to create a new column called `Contains Apple` that indicates whether or not the product name contains the word "Apple". We can use the SEARCH function to accomplish this.
The formula for the new column would be:
Contains Apple = IF(SEARCH(“Apple”, ‘Table'[Product Name], 0) ❱ 0, “Yes”, “No”)
This formula checks if the word "Apple" is present in the `Product Name` column. If it is, the new column will display "Yes", otherwise it will display "No".
Example 2
Suppose we have a table with a column called `Email Address`, and we want to create a new column called `Domain Name` that extracts the domain name from the email address. We can use the SEARCH function in conjunction with other functions to accomplish this.
The formula for the new column would be:
Domain Name = RIGHT(‘Table'[Email Address], LEN(‘Table'[Email Address]) – SEARCH(“@”, ‘Table'[Email Address], 0))
This formula first uses the SEARCH function to find the position of the "@" character in the email address. It then uses the LEN function to determine the length of the email address, and subtracts the position of the "@" character to get the length of the domain name. Finally, it uses the RIGHT function to extract the domain name from the email address based on the length calculated in the previous step.
The SEARCH function is a useful tool for searching for specific strings within other strings in Power BI. By understanding how to use this function, you can create more advanced calculations and aggregations in your data models. With practice, you can become proficient in using the SEARCH function and other DAX functions to create powerful data models in Power BI.
