FIND - Daxplained

FIND is a DAX function that returns the starting position of one text string within another, considering case sensitivity.

Syntax

FIND(
   FindText,
   WithinText,
   StartPosition,
   NotFoundValue
)

Argument	Properties	Description
FindText		The text you want to find. Use double quotes (empty text) to match the first character in within_text; wildcard characters not allowed.
WithinText		The text containing the text you want to find.
StartPosition	Optional	The character at which to start the search; if omitted, StartPosition = 1. The first character in WithinText is character number 1.
NotFoundValue	Optional	The numeric value to be returned if the text is not found; if omitted, an error is returned.

Return Values

Number that shows the starting point of the text string you want to find.

Remarks

Whereas Microsoft Excel has multiple versions of the FIND function to accommodate single-byte character set (SBCS) and double-byte character set (DBCS) languages, DAX uses Unicode and counts each character the same way; therefore, you do not need to use a different version depending on the character type.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
FIND does not support wildcards. To use wildcards, use SEARCH.

Example

The following DAX query finds the position of the first letter of “Bike”, in the string that contains the reseller name. If not found, Blank is returned.

Keep in mind, FIND is case-sensitive. In this example, if “bike” were used in the find_text argument, no results would be returned. Use SEARCH for case-insensitive.

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        TOPN ( 10, SUMMARIZE('Reseller', [Reseller], [Business Type])),
        "Position of Bike", FIND ( "Bike", 'Reseller'[Reseller], 1, BLANK () )
    ),
    'Reseller'[Business Type] IN { "Specialty Bike Shop", "Value Added Reseller", "Warehouse"}
)

Returns,

Reseller	Business Type	Position of Bike
Volume Bike Sellers	Warehouse	8
Mass Market Bikes	Value Added Reseller	13
Twin Cycles	Value Added Reseller
Rich Department Store	Warehouse
Rental Gallery	Specialty Bike Shop
Budget Toy Store	Warehouse
Global Sports Outlet	Warehouse
Online Bike Catalog	Warehouse	8
Helmets and Cycles	Value Added Reseller
Jumbo Bikes	Specialty Bike Shop	7

Other functions related to FIND are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/find-function-dax

Syntax

Return Values

Remarks

Example

Related functions