Forum Discussion
Row Level Security
I am facing a weird issue on Row Level Security.
I have user table comptising of user email address and store ID.
I have a store table which comprises multiple columns.This is a dimension table.
A relationship is established between user table and store table using store id(Active).
Due to certain reasons, we have a provided an inactive relation between store table and fact table using store ID. and have composed all measures using 'Userelationship' in DAX.
secondly, after publishing to service, when an external report views report, it throws an error, that userelationship is not supported with RLS.
could you please help me with this ?
RLS always applies for the condition where the expression is TRUE. Imagine that you have a dimension and an RLS table like the following screenshot:
As you notice, these are not paired via any relationships... You can always test for if an evaluation is TRUE or FALSE for your RLS by creating a temporary column to check the expression:
In this sample case, I'm looking to see if [email protected] is found my RLS table, if so, let's go look for him in the Dimension table as well:
Again, as long as it returns TRUE, then RLS can be applied... I checked for User1 and seems he's found.. If I change it to another user... USERABC and he's not found so his records will be false:
Now... since we know it works... replace the hardcoded UPN with USERPRINCIPALNAME to make it dynamic in your actual RLS:
I've uploaded a sample for your reference.
6 Replies
Hi Rajesh_Yarlagad ,
About a month ago I believe a few people began experiencing issues with their RLS no longer working when using USERELATIONSHIP. It is likely due to a change at Microsoft's side, more information can be found here:
https://learn.microsoft.com/en-us/dax/userelationship-function-daxFortunately, there are two methods to avoid this...
- Build out your RLS with an active relationship
- Disconnect your RLS table completely and build a TRUE statement against your 'Store' dimension. As long as it can evaluate TRUE to the expression, it will still work.
Unfortunately, we cannot provide an active relationship between store table and fact table due to some constraints. We cannot go that route actually. Could you please brief up second approach ?
RLS always applies for the condition where the expression is TRUE. Imagine that you have a dimension and an RLS table like the following screenshot:
As you notice, these are not paired via any relationships... You can always test for if an evaluation is TRUE or FALSE for your RLS by creating a temporary column to check the expression:
In this sample case, I'm looking to see if [email protected] is found my RLS table, if so, let's go look for him in the Dimension table as well:
Again, as long as it returns TRUE, then RLS can be applied... I checked for User1 and seems he's found.. If I change it to another user... USERABC and he's not found so his records will be false:
Now... since we know it works... replace the hardcoded UPN with USERPRINCIPALNAME to make it dynamic in your actual RLS:
I've uploaded a sample for your reference.
Alternate approaches for the error is explained here: https://www.sqlbi.com/articles/dax-limitations-with-inactive-relationships-and-row-level-security-rls/
Check if this helps!I have seen this article already. we explored treatas option before. But performance is not good with treatsas. So we cannot go that route as well
If USERELATIONSHIP is not working and TREATAS is slower, I can only can think of using VALUES with/without TREATAS.
Also consider:
Redo your calculate logic with different approach. Say, if your role is giving thousands of row, add a category or group and then bring the group to the user, this way it reduces the data to filter based on the user.
Below are the videos he talks about few scenarios. since you can't share the model, take a look:https://www.youtube.com/watch?v=qID6g_WmNEo
https://www.youtube.com/watch?v=s7PPArgNzd8
https://www.youtube.com/watch?v=2caokSoWWUA
I am sure you might have tried already, but providing to have one more review! If your RLS is too complicated and affects more dax functions, then performance may be affected. It also depends on the performance of your data model.https://learn.microsoft.com/en-us/power-bi/guidance/rls-guidance
