Forum Discussion
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 for rows where the e-mail address suffix contains a specific domain (e.g. @domain.com).
For this, I have created following Table filter DAX expression in the Roles:
FIND("@domain.com", [Email], 1, -1) = 1
My assumption was that the following DAX expression should return all data where the email field contains at "@domain.com". However, when running a "View as" using this role, the following error is returned:
Error Message: Specified method is not supported.
Model Default Mode: DirectQuery
From my understanding this means that the Find() method is not support on a Table filter DAX expression when using Direct Query. What would be other solutions to filter on a part of the text in a field in RLS?
11 Replies
Hi, Anonymous
I think this error message means that the FIND() function isn’t supported in Direct query mode, you should know that Direct query mode has a big influence on the use of DAX statement.
I think you can try the CONTAINSSTRING() function or CONTAINSSTRINGEXACT() function and create a DAX statement like this:
CONTAINSSTRING ([Email],"@domain.com")CONTAINSSTRINGEXACT ([Email],"@domain.com")More info about CONTAINSSTRING() 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.
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.
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?
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.
Hi, Anonymous
According to your description, you want to get a solution in the “Transform data”. I think you can try to add a conditional column, here are the step:
And you can get what you want, like this:
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
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.
It appears unfortunately that also conditional columns cannot be used in DirectQuery.
All of the things we tried before seem to be caused by the DirectQuery functionality, which seems to be very limiting. I have tried switching the storage mode to Import Mode (instead of DirectQuery), but unfortunately this doesn't seem to work, no data is returned after switching to Import Mode (table is empty).
Given that my issues seem related to the DirectQuery mode, I'm not sure if I can easily share a .pbix file directly here (as it would require access to the source data). However, I can share that the solution I'm building is based on the Teams Call Quality dashboards (more specifically: CQD Helpdesk Report.pbit) where I'm trying to add RLS based on e-mail (UPN) suffix. The actual columns I'm trying to use for filtering the suffix are called [First UPN] and [Second UPN] in the linked .pbit file.
The expected result would be to have a PBI role that can only view data for rows where [First UPN] or [Second UPN] ends in @domain.com. The PBI role should not be able to view rows where [First UPN] or [Second UPN] is not ending in @domain.com.
Hi, Anonymous
According to your description, you said that you have switched the storage mode to Import Mode, which means we can use the DAX formula, right? And you want to create the PBI role that should not be able to view rows where [First UPN] or [Second UPN] is not ending in @domain.com, you can try my steps:
This is my test data:
- I created a calculated column:
Column = IF(CONTAINSSTRING([First UPN],"@domain.com")&& CONTAINSSTRING([Second UPN],"@domain.com"), 1,0)- I created a new role like this:
And you can get what you want.
You can download my test pbix file here
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.
Hi Robert,
I tried switching to Import Mode, but it didn't work for my report. No data is returned after switching to Import Mode (table is empty). It seems the report can only work using DirectQuery mode.
See also the sample: https://docs.microsoft.com/en-us/microsoftteams/cqd-power-bi-query-templates > CQD Helpdesk Report.pbit
Apologies to bump this topic up, but does anyone see a possibility to add RLS here on the shared report? (sample: https://docs.microsoft.com/en-us/microsoftteams/cqd-power-bi-query-templates > CQD Helpdesk Report.pbit)
The goal would be to define a role based on the [First UPN] value domain suffix. (e.g. Role A = [First UPN] ending in @domain.com)
