Row Level Security - MdxScript(Model) (45,60) Calculation error in measure

[Anonymous]

Hi everyone,

I have developed a dashboard which has 9 pages and published it to BI Service.
The requirement was to set up page level access restriction but since this is not possible with Power BI, we tried RLS. ( to restrict the visuals)
I created a view in Snowflake ACCESS_CONTROL_VIEW which has below columns. The storage mode is mixed as I am using time dimension in my semantic model as well.
The ACCESS_CONTROL_VIEW has following columns -

user_id STRING NOT NULL,
page_name STRING NOT NULL,
restricted INTEGER NOT NULL

Once, I set up RLS for the view, I created Access measure to restrict access to visuals.
ACCESS = IF(LOOKUPVALUE(ACCESS_CONTROL_VIEW[RESTRICTED], ACCESS_CONTROL_VIEW[USER_ID],USERPRINCIPALNAME())=1,1,0)

I gave access to all users at first & RLS worked as expected -

Later I changed the restiction for user B and it did not work properly.

After updating the table & refreshing the view -

User B should not be able to see P3 but is able to view P3 and they should be able to see P1 & P2, but they are unable to view and this is the error screenshot - 

This is working as expected in Power BI Service when I test it out using other role, but it is not working properly in Power BI Service.

I tried refreshing the semantic model, re-publishing it, tried to re-do the RLS, there are no duplicate values for a given user. Looking for any other suggestions. Thankyou.

[Anonymous]

I was able to bypass the error by changing the connection mode from direct query to mixed for  ACCESS_CONTROL_VIEW.

And I was able to achieve the required functionality i.e. Certain users can access certain pages based on which page they have access to. This is not a straight forward and secure way but it works for now. 

Youtube Link - 

https://www.youtube.com/watch?v=eHzkn3pSJ4g&t=300s&pp=ygUWcGFnZSBzZWN1cml0eSBwb3dlciBiaQ%3D%3D

[Anonymous]

It's a great pleasure to know that you've found a solution. To help other members of the community, can you mark your response as a solution? This way others who are experiencing similar problems can find the answer faster.

If you need any help with this, please feel free to let us know.

Thank you for your cooperation and support!

Best wishes

[lbendlin]

RLS is restricting access to rows, not to visuals.  All users will see all visuals, but for some users the UX will be bad as the visuals will have no data.

[Anonymous]

Hi Ibendlin,

Thankyou for your response. Yes, I an aware. My idea was to restrict data whenever a user does not have access( as specified in the access_control_view), they will be able to see the visual but not the data. This solution was fine for our requirement as we don't want to expose some of the financial metrics. For example, this is how it looks when a user doesn't have access.

The measure Access has been applied to filter for each visual.

I know this is not a direct way of doing things, but found this workaround and it is working for others who have implemented this but stopped working for me a little later. 

[Anonymous]

Hi, @Anonymous 

If you've already resolved the current issue, you can mark your response as a "solution" so that other users know that the issue has been resolved and help others in the community. Thank you very much for your cooperation and participation!

Best Regards

Jianpeng Li

[lbendlin]

If your users represent sufficiently different audiences and access scopes then create separate reports.

[Anonymous]

We thought of implementing the same, but managing multiple reports would be a hassle. 

Right now we only have 1 business user but in the coming year there would be a lot more. This would mean, if there's a change that needs to be done to a common visual, then we would have to do the same for multiple reports to keep the reports up to date. Hence, we're trying to look for an alternative solution.

Is there any other way, where I could restrict the data in visuals for different users based on their user_ids and access ( either true or false ) ? 

[lbendlin]

Your job as a report designer is to make it easier for the users to get to actionable insights. Instead, you waste time trying to make a harder.

[Anonymous · Accepted solution]

I understand that the easier approach is to create separate reports and publish them, but we are trying to reduce the maintenance of multiple reports.