Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX string filtering function in RLS with DirectQuery

I have a Power BI report that relies on DirectQuery to fetch data. One of the columns in the data is an e-mail address.  I would like to create a Power BI role which only allows a user to view data ...
Need Help
v-robertq-msft's avatar
v-robertq-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

I came out with another way to find domain names using DAX, you can try to use the RIGHT() function to get the domain name and check it, like this:

Column =

IF(RIGHT([Email],11)="@domain.com",1,0)

Then you can filter the table as [Column]=1

 

More info about RIGHT() function in DAX

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for your help Robert, appreciated! Unfortunately, it appears also the RIGHT() function is not supported in DirectQuery. Trying without the IF() by creating following column already fails: 

 

Column = RIGHT([Email],11)

 

Following error is produced: 
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..

 

It seems the string functions that can be used in DirectQuery are very limited.. Are there other options to match part of a string in RLS with DirectQuery? 

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    There is also a function in DAX called SEARCH() that can achieve this:

    Column=
SEARCH(“@domain.com”,[Email],1,0)

    If it’s not supported in Direct query, I guess there’s no way to achieve this. You have to find solution in other ways.

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Unfortunately using SEARCH() results in the same error message: 
      OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..

      Is there anything that we could do from the Transform Data section maybe, to produce a separate column that would contain the email domain suffix?