cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pradeep Regular Visitor
Regular Visitor

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

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
2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

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


@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:

 

q6.PNG

 

 

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

 

q7.PNGq8.PNG

 

 

Reference:

Manage security on your model

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator v-qiuyu-msft
Moderator

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

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 REPLIES 12
Sunkari Member
Member

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

Pradeep Regular Visitor
Regular Visitor

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

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.

Moderator v-qiuyu-msft
Moderator

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


@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:

 

q6.PNG

 

 

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

 

q7.PNGq8.PNG

 

 

Reference:

Manage security on your model

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Pradeep Regular Visitor
Regular Visitor

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

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

Moderator v-qiuyu-msft
Moderator

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

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Pradeep Regular Visitor
Regular Visitor

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

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

 

 

Moderator v-qiuyu-msft
Moderator

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


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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Pradeep Regular Visitor
Regular Visitor

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

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

Moderator v-qiuyu-msft
Moderator

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

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.