Faster CONTAINSSTRING alternative for RLS

[rkaushik20]

I have tried multiple alternatives mentioned in https://docs.microsoft.com/en-us/power-bi/guidance/rls-guidance 

but RLS is still not fast. For example, without RLS, reports work very fast (almost instantaneous) but after applying RLS, some of them take a very long time (almost 1 min in some cases). So I'm guessing it comes down to a faster alternative for CONTAINSSTRING dax query.

Scenario: 

ROWS: ~300K 

Security logic:  

1. string concatenated email id's of users (;abc@msft.com;bcd@msft.com;cde@msft.com) 

2. containsstring([VIEWERS],(lower(USERPRINCIPALNAME())))=TRUE for the role where VIEWERS contains this concatenated email id's 

Am I doing anything wrong in the logic above? We have a lot of business rules, hence concatenating email id's in the backend based on the logic  seemed like the best option. 

Any suggestions would be helpful. 

[v-xiaoyan-msft]

Hi @rkaushik20 ,

Use RLS for all your users so that Power BI has to query the backend server separately and cache separate reports for every user, which might slow down the report performance. This is a design issue and may have little to do with your guess.

I searched for power bi relationship bi but not found documents that mentioned how to mapping rows without affect performance.
Since power bi data model stored data at AS instance, maybe you can take a look at AS tabular model RLS related document:

Optimizing RLS performance with the Query Store 

In addition, you can also consider creating a weak relationship mapping between user and store, then you can add filters and conditions in your RLS expression to help to map records more accurately.

Relationships in analysis services tabular models  

Best Regards,
Community Support Team _ Caitlyn

[rkaushik20]

hi @v-xiaoyan-msft , 

Thanks for a swift response. Are you suggesting that our security design is an issue or it is a Power BI design issue? if yes, could you please suggest any recommended RLS security for user email-based setup?

Thanks again for your help.

Best Regards