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
Community Support
5 years ago

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:

 

  1. I created a calculated column:
Column =

IF(CONTAINSSTRING([First UPN],"@domain.com")&&

CONTAINSSTRING([Second UPN],"@domain.com"),

1,0)
  1. 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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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)