Not able to use string DAX functions in RLS filter criteria with DirectQuery

[sundeep579]

I have a scenario wherein I need to do apply SQL LIKE '%USERNAME()%' filter in Table filter DAX expression to setup Row level security to work with a SQL server setup using DirectQuery. I tried to use FIND(), SEARCH(), LOOKUPVALUE() DAX functions in Table filter DAX expression box, but I keep getting an error sayng 'Function SEARCH is not allowed as part of the row level security expression on DirectQuery models'. Is there any workaround to this ?

All I need is to setup a [MyColumnName] LIKE '%USERNAME()%' filter in Table filter DAX expression. Is there anyway to set this up in Power BI ?

[bblais]

I was able to make it work with SEARCH using the following.  This is searching my Geography[City] column for any city with "ville" in it.

IFERROR(IF(SEARCH("*VILLE*",Geography[City])>0,TRUE(),FALSE()),FALSE())

 Hopefully you can adapt it to your scenario...

[bblais]

My apologies, this is not on a DirectQuery model so it may not work, as you suggested already. I will attempt to solve it for a DirectQuery model as well.

[sundeep579]

Yes, I am getting this error only while using DirectQuery whereas in Import mode everything is working fine.

[bblais]

It looks like it is not currently possible to do the "LIKE" search within SQL DirectQuery RLS, as you have indicated.  Perhaps you can piece together a string that would be an exact match so you don't have to use LIKE?  I haven't seen your data, but if your database has only username in it but you are getting DOMAIN\username from the call to the USERNAME() DAX function, you could parse out the domain name and slash so then it would be exact match...