In the previous blog post, I explained how to implement RLS and OLS (Object Level Security) at the same time for one user in one dataset (https://jihwankimcscp.wixsite.com/supplychainflow/post/implement-both-rls-and-ols-together-in-power-bi)
In this writing, I want to share what would be shown on the report page if one user is assigned under two different roles in RLS setting. In fact, this is one of the famous PL-300 exam questions, and this makes to consider whether the result would show AND-like-result or OR-like-result.
In order to test this, I created a simple sample dataset like below.
Sample datamodel
Sales table
And, I created two table visualizations that are shown in the below figure.
Setup RLS for country = Netherlands.
Setup OLS for not-showing-product-column -> this needs to be further setup in Tabular Editor.
Setup RLS for product = P03.
Now, two RLSs and one OLS are setup.
Firstly, let's test implementing one RLS and one OLS at the same time. By selecting two roles from RLS and OLS like the below figure, the result shows error. And the reason is clearly explained in the error message. The previous blog post (https://jihwankimcscp.wixsite.com/supplychainflow/post/implement-both-rls-and-ols-together-in-power-bi) showed how to solve this issue.
Secondly, only selecting Netherlands RLS shows correct information.
Thirdly, only selecting Product03 RLS shows correct information.
Lastly, selecting both Netherlands RLS and Product03 RLS shows like the below.
I published this to the Power BI service, and assign one user (Alicia Thomber) under two roles like below.
And then, give a viewer right to Alicia Thomber (Other than giving the viewer right, RLS will not work properly).
Of course, the same result as Power BI desktop is shown on Power BI service.
After seeing the result, I still cannot know what to say. Is this AND-like-combination, or OR-like-combination?
As shown above, Belgium's partial quantity is shown, Italy's partial quantity is shown, and P01's partial quantity is shown.
Even this is one of a famous questions of PL-300 exam, I still cannot know what is the correct answer. Do you know? Your insight might help someone to pass the exam.
I hope this helps to deep dive and start to learn & test more about setting up RLS in Power BI.
Comments