Syntax Bug in FIND() and SEARCH()

[MattAllington]

Both the FIND function and SEARCH function in DAX (Power BI Desktop, March 2019 take 2 mandatory parameters and 2 optional parameters.  If you write a function (say a calculated column) and omit the 2 optional parameters, you get an error.

Calc Column in the Products table  

Test = SEARCH("Mountain",Products[ModelName])

When I write the following, it works.

Test = SEARCH("Mountain",Products[ModelName],1,0)

The last 2 parameters cannot be optional if they are required to make it work. 

Adventure Works basic - Copy (2).pbix

[v-yuezhe-msft]

@MattAllington ,

As your post, the latter two parameters are optional, however, as described in this article, if the find_text cannot be found in within_text, the formula returns an error. This behavior is like Excel, which returns #VALUE if the substring is not found.

You can also change the first DAX to the following:

Test = SEARCH("Mountain",Products[ModelName],,0)

There is also a discussion in the following similar thread.

https://community.powerbi.com/t5/Desktop/DAX-FIND-or-SEARCH-part-of-a-string-in-a-column/td-p/134518

Regards,
Lydia

[MattAllington]

Hi Lydia

Can you please show me a working formula that doesn't use the optional parameters?  If the parameters are optional, the formula should work without using them.   For comparison, the TOTALYTD function has optional parameters

=TOTALYTD([Total Sales],Calendar[Date]) will work.  There are 2 other parameters that I have not used, becuase they are optional  [Filter],[Year End]

So please show me how to write a working function using SEARCH and FIND without the optional parameters.  If you can't do that, then clearly the parameters are not optional.

[Sinology]

I just faced the same issue and wanted to report on it. I try to use FIND or SEARCH in a calculated column. If I don't specify the 3rd and 4th parameter I get #ERROR. If I specify the parameters I get a numeric result (as in, the function return the position of the text I'm trying to find.