How to generate Power BI report based on logged-in user in Dynamics 365 CRM Online (restricted to his own record)

By | June 14, 2019

Note – Data in report will not follow security roles of logged-in user.

Introduction

In this blog we will see how to generate Power BI report based on logged-in user in Dynamics 365 CRM Online.

Step 1

Create a report in Power BI desktop.

Power BI

Step 2

Create a role in Power BI desktop.

Go to modelling -> manage roles -> Create new role here.

Power BI

Notes:

While creating DAX expression-

  • Create condition like above
  • [domainname]-This contains email id of user
  • Userprincipalname ()-returns logged-in user email id
  • We are comparing with email id because Power BI returns users email id only

Difference between Username () and Userprincipalname ()-

Username () return user in domain/user format – (Logged-in user on system).

Power BI

Userprincipalname () return user in user (email id) format.

Step 3

After creating a role apply that role.

Go to modelling -> view as role -> select role which you have created.

Power BI

Step 4

Create relationship in Power BI desktop between system user’s entity and other entity which you are using while creating report.

Go to Home -> Manage relationships -> new

Create new relationship here.

Select primary keys as shown below.

Power BI

Step 5

Now publish report to Dynamics 365 CRM and pin that report on dashboard.

Go to CRM and add that dashboard:

  • Publish report from here.

Power BI

  • Select your workspace.

Power BI

  • Click on Pin Live Page to make Power BI report visible on dashboard.

Power BI

  • Give a name to your dashboard and make it live.

Power BI

  • Go to CRM and add this dashboard

Power BI

Here choose your workspace and dashboard.

Power BI

Step 6

Now go to Power BI service.

Power BI

Click on your dataset -> Security.

Power BI

Add user’s email for whom you want the Power BI dashboard to be visible in CRM.

Power BI

Step 7

After adding on dashboard share that dashboard to other users.

Power BI

Step 8

After sharing the dashboard please log in with the user credentials for which you have shared the report and add that dashboard. You can see report with user logged-in data.

Report of admin (Report Creator):

Power BI

Report of user 1:

Power BI

Report of user 2:

Power BI

Conclusion

We are able to see Power BI reports in Dynamics 365 CRM Online based on logged in user.

Cut short 90% of your manual work and repetitive data entry!

Get 1 Click apps and say goodbye to all repetitive data entry in CRM –
Click2Clone – Clone/Copy Dynamics 365 CRM records in 1 Click
Click2Export – Export Dynamics 365 CRM Report/CRM Views/Word/Excel template in 1 Click
Click2Undo – Undo & Restore Dynamics 365 CRM data in 1 Click

12 thoughts on “How to generate Power BI report based on logged-in user in Dynamics 365 CRM Online (restricted to his own record)

  1. Remya

    Can we implement the same in Dynamics 365 portal?

    1. inogic

      Yes, you can implement the same in Dynamics 365 portal.

  2. Mark

    Thank you for the demo. This method displays through PowerBI only records the logged in user owns. Can we use a different method in PowerBI to show all records the logged in user has read access to via their D365 security roles? For instance, say the user has read access to all Opportunities in their Business Unit, not just the Opps they own. Can a PowerBI report show all records in their BU?

    1. Inogic

      No. We can’t get records depend on security role of logged in user.

      Thanks!

  3. PowerBIUser

    Can the user has the ability to see his own record and ability to see all data as well.
    Basically i want when User Lands in he should be able to see his own data but he can also have power where he can see complete data set.

    1. Inogic

      It’s not possible in the same report because we are creating role which is applied on report dataset. Instead you can create two report i.e. with and without role. Now, to make it visible on CRM Dashboard, please click on Pin Live Page and select the same dashboard for both report. In this way, user will be able to see both report on same Dashboard.

      Thanks!

  4. Andrew

    Can developers implement dynamic layouts based on role? using the example above could user 1 see all 4 of the tiles while user 2 only see 3 of the tiles? and if user 2 could not see “my open opportunities” tile would the other 3 tiles reflow to avoid having an empty tile in the upper left most tile area on the dashboard?

  5. Rizwan

    HI,

    THis is working fine in PowerBi desktop but after publish the report data is not showing logged in user wise.

    1. Inogic

      The Row Level Security in the Power BI works only for Non-Admin users not for the Admin user.

      Now admin user is not a user who has the System Administrator role. The Admin user in terms of Power BI is the user who owns the Power BI report. The admin user of Power BI has access to all the report data as he is the owner of the report.

      So if you are opening the Power BI report using the admin user then RLS will not work as he is the owner of the report as well as the dataset. If you try the same using a non-admin user, it will apply Row Level Security properly.

      Please find the below link for your reference:

      https://docs.microsoft.com/en-us/power-bi/service-admin-rls

      And, if you still face the issue, then let us know the steps which you are performing to help you further.

      Hope this helps.

      Thanks!

  6. Soundharya

    Hi,
    How to add multiple filters for related entity with or condition, i have added filters for multiple entities but it is acting as and condition.

Comments are closed.