FIND Function is a Power BI TEXT function in DAX which returns the starting position of one text string within another text string.
FIND function is a case-sensitive and does not support wildcards.
SYNTAX
FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
find_text is the text that you want to find with in text. To match the first character in within_text, use double quotes(” “).
within_text is the text in which you want to search.
start_num is the character at which to start the search. It is an Optional, If not specified then start_num = 1. The first character in within_text is character number 1.
NotFoundValue is the value that should be returned when any matching substring is not found . it is an optional.
Lets look at an example of FIND function.
Using FIND function to get the Starting position of text
Following Dax expression uses FIND function to find the starting position of Text “How” within string
Findtext_Starting_Position =
FIND("How", "Hey ! How are you")
Once you commit the DAX, Lets darg the Measure Findtext_Starting_Position into Card visual.
Using FIND function to search within string from specific position
You can also specify a start position in FIND function to search the text within string from specified position.
Lets say, you have a string “Hey ! Hey How are you “, as you can see text “Hey” are coming two times in string. The position of the first letter of “Hey” is at 1st position in the first occurrence of “Hey” in string. While the position of first letter of “Hey” in the second occurrence is at 7th position.
Lets see what happens, when you use FIND function to get the position of text “Hey” within string in such case.
Findtext_Starting_Position =
FIND("Hey", "Hey ! Hey How are you")
Once you commit the DAX, Lets see the output of Dax just drag it into card visual.
You can see, it returns 1, ofcourse that is the first position of letter in “Hey” which is the first occurrence of “Hey” within string.
Suppose, if you want to skip the first “Hey” from search then you can provide a start position in FIND function.
Lets provide a start position as 4 in find function.
Findtext_Starting_Position =
FIND("Hey", "Hey ! Hey How are you", 4)
FIND function with case-sensitive values
In above string if you try to find text “HOW” which have all letter in upper case, then FIND function will not find this text within String “Hey ! Hey How are you” as text that you want to find have all letter are in upper case while in string the letter How has only H in upper case while o and w is in lower case.
So basically FIND function matches the values within string in case-sensitive fashion.
Lets see it in acition, and try to find text “HOW” in string “Hey ! Hey How are you”.
Also we will provide a Not found value to FIND function that will be returned when it does not find any matching text within string, if not provied it will give an error.
As you can see, In following DAX we have provided a value 0, that will be returned when FIND function does not find any matching text in string, you can also give any positive and negative integer value or BLANK().
Findtext_Starting_Position =
FIND("HOW", "Hey ! Hey How are you", 4, 0)
Lets see, the output, and you can see it returns 0 that means it does not find any match for “HOW” that means it is case-sensitive.
