Forum Discussion

sundeep579's avatar
sundeep579
Frequent Visitor
9 years ago

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

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 ?

Need Help

4 Replies

  • bblais's avatar
    bblais
    Icon for Resolver III rankResolver III
    9 years ago

    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's avatar
      bblais
      Icon for Resolver III rankResolver III
      9 years ago

      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's avatar
        sundeep579
        Frequent Visitor
        9 years ago

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