RLS in report embedded in Dynamics CRM

[rajulshah]

Hello,

I have embedded my Power BI Report in Dynamics CRM as a Web Resource, wherein I have passed two roles:

1. Open (1=1)

2. Restricted ([Column]=USERNAME())

What I need is that when a manager logins, he needs to see all the data, and when a user logins, he can see only his data. Hence, I am using below JSON -

var jsonData = JSON.stringify({
"GroupId": "xxxxxx-xxx-xxxx-xxxx-xxxxxxx",
"ReportId": "xxxxxx-xxx-xxxx-xxxx-xxxxxxx",
"Identity": {
"Username": username,
"Roles": ["Open","Restricted"],
"Datasets": ["xxxxxx-xxx-xxxx-xxxx-xxxxxxx"]
}
});

After implementing RLS in embedded Power BI Report as mentioned in https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security, I can see restricted data for that particular user but when a manager logins it doesn't show any data, where I expect that the manager sees all data.

Please guide us what am I missing in the flow.

[v-piga-msft]

Hi @rajulshah,

For your sceanrio, I'm afraid that the issue is caused by the role you created in Power BI Desktop.

Do you have the manager username () in the table? 

You could try to create the role similar with the formula below in Power BI Desktop.

if(userprincipalname() = "aa@dale.onmicrosoft.com",true(),[Col1] = "b")

If the username is maneger username then the function is true and it will show all data.

More details, you could refer to the attachment.

Best Regards,

Cherry

[rajulshah]

Hello @v-piga-msft,

I have a table of Users.

Hence whenever a User logins, he should be able to see his own data but whenever anyone other than User logins, he should be able to see all the data.

Now, what I am unable to achieve is how to allocate users to different roles.

Suppose, I want User : A and B for the Open role.

Manager : C and D for Restricted role.

Am I clear with my requirements?

[rajulshah]

Hello @v-piga-msft,

Do you have any solution for the above requirement?

Any help would be great.

[v-piga-msft]

Hi @rajulshah,

It seems that your want to have two roles: manager and user.

You could refer to this blog which introduce the details about how to create the roles.

In addition, we always assign roles under Security in Power BI Service.

Best  Regards,

Cherry

[rajulshah]

Hello @v-piga-msft,

For embedded report, I found the following blog that states that Security in Service isn't effective.

https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security (Considerations and limitations).

Based on the blog you suggested, it's really helpful blog but I want to use 2 roles. 

 My question is, if you pass one username and 2 roles, how does it get classified that which role it has to be?

Am I yet clear?