FIND Function DAX

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.


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

“Hey ! How are you”.
As you see, the starting position of first letter in text How is 7. Lets see what do you get from FIND function.
Findtext_Starting_Position =
FIND("How", "Hey ! How are you")

Once you commit the DAX, Lets darg the Measure Findtext_Starting_Position into Card visual.

As you can see, it returns 7 that means the starting position of first letter of text How within string is at 7.

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)
Lets see, the output of DAX, and you can see this time it returns the position of text “Hey” is 7.
That means search is started from position 4 in string.

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.






SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 18 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.