FIND/SEARCH in direct query mode

[Anonymous]

Hi All,

I have sql DB connectivity with direct query and I want to create a custom column with FIND/SEARCH dax. However I am getting error : "Function 'FIND' is not allowed as part of calculated column DAX expressions on DirectQuery models."

I have already turned on the setting from options to allow unrestricted measures in direct query mode but still not able to use. Can you help me?

Thank you in advance,

Niket

[ManishSingh]

Hi All,

I am trying to use Search or ContainsString function in Direct Query mode but getting the same error that these functions are not supported in direct query model. However in the list of optimized dax functions they are listed.

Also, there is no option in the latest power bi desktop version to allow unrestricted measures in direct query mode.

Please help as this issue has become a major bottleneck for us.

[v-haibl-msft]

@Anonymous

I’ve got response from the Product Team.

DirectQuery pushes calculated column calculations to the underlying database, therefore some DAX functions are not supported as the underlying database doesn't have corresponding operation. In this case, SEARCH and FIND functions are actually supported but the user must specify all 4 arguments: SEARCH("-", [Email], 1, 0). That's because the 2 argument version of the functions returns an error when a match is not found while the 4 argument version resturns a number which the user can use to decide whether a match is found.

Best Regards,
Herbert

[Anonymous]

What do you mean by [Email]? for me the second parameter is only showing up measures to select from. there is no option to select from existing columns. i am working against SAP HANA direct query.

[v-sihou-msft]

@Anonymous

I reproduced same issue as yours. We have reported it internally.

Regards,