how to display reports only for logged in user in PowerBi.com ?

[Pradeep]

Hi, I am developing an employee report in powerbi.com. All the data is in Azure SQL DB and we have billions of rows of data. I designed report in Power BI Desktop with direct query. I exported my report into Pbix file to power bi.com Now for Example, Employee1 logs into PowerBI.com and login using an email ID Employee1@microsoft.com. Now when this Employee1 sees report, we would like to get data from Azure SQL only for that Employee1 and show the report in powerbi.com. What i meant is that, we only show the report that contains logged in user data only. how do we pass powerbi.com logged in user information to query in real time. Thank You -Pradeep

[v-qiuyu-msft · Accepted solution]

---

@Pradeep wrote:
Hi, I am developing an employee report in powerbi.com. All the data is in Azure SQL DB and we have billions of rows of data. I designed report in Power BI Desktop with direct query. I exported my report into Pbix file to power bi.com Now for Example, Employee1 logs into PowerBI.com and login using an email ID Employee1@microsoft.com. Now when this Employee1 sees report, we would like to get data from Azure SQL only for that Employee1 and show the report in powerbi.com. What i meant is that, we only show the report that contains logged in user data only. how do we pass powerbi.com logged in user information to query in real time. Thank You -Pradeep

---

Hi @Pradeep,

If I understand correctly, you want to set the user can only view his/her own data when he/she access the report from Power BI Service, right?

In this scenario, what you need is Row-Level-Security. As you have add the filter like "[Emp_Name] = USERNAME()" in Power BI Desktop, you need to apply this role for the dataset of the report. Assume in the Azure database has one column (eg: UserName ) contains user's User Principal Name (UPN) which is similar to email address, you can set the filter like below:

Then in Power BI Service,  you can add a member to the role, then Test as Role.

Reference:

Manage security on your model

Best Regards,
Qiuyun Yu

[leipapuu]

I had a similar problem, and this message really hepled me on my way. Thanks.

However, I need some more filtering to my role. I wanted to filter my contact records with the owningbusinessunit. I have the same businessunit column in my user table, where I also have the username column.

I have 'systemusers'[domainname] that matches the USERNAME() and I have contacts'[OwningBusinessUnit] that should match the field 'systemusers'[businesunitid.name].

For testing, I created this measure. It works fine in my report visualization, and returns the business unit for the logged in user:

Testing Business Unit = LOOKUPVALUE(systemusers[businessunitid.name]; systemusers[domainname]; USERNAME())

In the Manage Roles window I needed to replace the semi-colons with commas, for regional (EMEA) purposes propoably:

For contacts table:

[OwningBusinessUnit] = LOOKUPVALUE(systemusers[businessunitid.name], systemusers[domainname], USERNAME())

 I found this tutorial also useful:

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular

Hope this helps

[v-qiuyu-msft · Accepted solution]

---

@Pradeep wrote:
Hi, I am developing an employee report in powerbi.com. All the data is in Azure SQL DB and we have billions of rows of data. I designed report in Power BI Desktop with direct query. I exported my report into Pbix file to power bi.com Now for Example, Employee1 logs into PowerBI.com and login using an email ID Employee1@microsoft.com. Now when this Employee1 sees report, we would like to get data from Azure SQL only for that Employee1 and show the report in powerbi.com. What i meant is that, we only show the report that contains logged in user data only. how do we pass powerbi.com logged in user information to query in real time. Thank You -Pradeep

---

Hi @Pradeep,

If I understand correctly, you want to set the user can only view his/her own data when he/she access the report from Power BI Service, right?

In this scenario, what you need is Row-Level-Security. As you have add the filter like "[Emp_Name] = USERNAME()" in Power BI Desktop, you need to apply this role for the dataset of the report. Assume in the Azure database has one column (eg: UserName ) contains user's User Principal Name (UPN) which is similar to email address, you can set the filter like below:

Then in Power BI Service,  you can add a member to the role, then Test as Role.

Reference:

Manage security on your model

Best Regards,
Qiuyun Yu

[Niranjan]

Do you know how to do the same thorugh Tabular--SSAS?

This will really help us.

Thanks in advance.

[Pradeep]

Hi @v-qiuyu-msft,

In my sql database the column like Emp_Email it contains the user mail address. so i created the filter in power bi desktop like [Emp_Email]=username().

But when i click on view as roles button in power bi desktop no data is displayed even when i clicked on my role what i was created earlier.

Please tell me where i did mistake.

Best Regards,
Pradeep

[v-qiuyu-msft]

Hi @Pradeep,

The reason is username() will return a user in the format of DOMAIN\User Within Power BI Desktop, while it will return the user's User Principal Name (UPN) which looks similar to an email address in Power BI Service.

You need to publish the report to Power BI Service, add the member to the role and test as role. Please follow the steps mentioned in the article posted in my original reply.

Best Regards,
Qiuyun Yu

[Pradeep]

Hi @v-qiuyu-msft,

After published my pbix file into powerbi.com. then i logged into powerbi.com, right click on my dataset select security option and add the user. after added the user next click on test as role, now only the report contains the data of particlular user.

But it is not good way for end users, because everyone go to the dataset select security option and add the user. after added the user next click on test as role now only the report shiows the data of particlular user.

So, That's why i want the scenatio like as who ever logs into powerbi.com then immedialtely power bi report shows data of  only logged in user.

Best Regards,

Pradeep

[v-qiuyu-msft]

---

But it is not good way for end users, because everyone go to the dataset select security option and add the user. after added the user next click on test as role now only the report shiows the data of particlular user.

Hi @Pradeep,

All those steps are done by dataset owner instead of the end user. The Test as Role just let the dataset owner impersonate the member who has been added in the role, to know how the result will be when this member access this report. For example, if you add Employee1@microsoft.com to the role when you check Security, you can let this user Employee1@microsoft.com log on his Power BI Service and run this report to versify the result.

Best Regards,
Qiuyun Yu

[Pradeep]

Hi @v-qiuyu-msft

When ever the user like Employee1@microsoft.com log on into powerbi.com then no datasets and reports are not available for the user.

Please tell me whenever Employee1@microsoft.com log on into powerbi.com then entire power report is displayed as what i did earlier for other employee. rather than once again publish pbix file into powerbi.com of every user like what i added users in the role earlier.

Best Regards,

Pradeep

[v-qiuyu-msft · Accepted solution]

Hi @Pradeep,

Did you publish the report to My Workspace or other group workspace? If you publish to My Workspace, you need to pin the report visual to the dashboard and share the dashboard with Employee1@microsoft.com. If you publish the report to group workspace, you need to grant Employee1@microsoft.com view only permission. Then Employee1@microsoft.com can access the report to see his/her own data.

Best Regards,
Qiuyun Yu

[Pradeep]

Hi @v-qiuyu-msft

Thanks, Now its working fine after i shared my report  to other employees with view only permission what i added the users in my role.

Best Regards,

Pradeep

[pbiforum123]

As you have mentioned in your respose instead of using Dataset --> Security --> Test as Role --> Provide the userid, how can i directly show only the required data which the logged in user is supposed to see. Please explain...

[Sunkari]

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

[Pradeep]

Hi,

I have seen the above document already and implemented the RLS for my report like "[Emp_Name] = USERNAME()" but i want the fucntionality like when ever the user logs into powerbi.com then only the displayed the reports of particular user.