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
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?