Step-by-Step Guide to Creating Power BI Reports with Dynamics 365

You can make helpful reports with Power BI using your Dynamics 365 data. Many people have problems like keeping private records safe. They also need to help everyone learn new tools. Reports can get slow when there is more data. You can fix these problems with some steps. You can set up row-level security. You can give training based on each person’s job. You can also make data models that work well. If you follow clear steps, your team can use your data better.

Key Takeaways

• Get your tools, licenses, and permissions ready first. This helps you make and share reports without problems.

• Pick the best connector and way to bring in data. This helps your report stay fast and up to date. It also gives you the features you need.

• Make strong data models and use simple visuals. This makes your reports easy to read and quick to use.

• Try features like 'Visualize This View' to make reports fast. Put reports in dashboards or apps to share with your team.

• Keep your data safe by setting up security roles. Use row-level security and check report access and speed often.

Prerequisites

Tools and Licenses

You need some tools and licenses before you begin. Make sure Power BI Desktop is on your computer. You also need to use the Power BI service to share reports. Check if you have a valid Dynamics 365 license. Some things, like saving or turning off connectors, may need a Power Automate license.

Tip: Update your software often. This helps stop problems when you connect to your data.

Permissions

You need the right permissions to make and share reports. In Power BI, you must have Build permission on the semantic model. This lets you make reports, export data, and use more features. You can get Build permission in a few ways:

• Get a Contributor role or higher in your workspace.

• Get permission from the semantic model owner.

• Get permission through app publishing settings.

• Have reports or dashboards shared with you that include Build permission.

If you do not have Build permission, you can only look at reports. You cannot change or export data. You might see a message to ask for more permissions.

Dynamics 365 Settings

You must set up Dynamics 365 before you connect it to Power BI. Follow these steps:

1. Sign in to the Azure portal. Go to Azure Active Directory, then App registrations.

2. Register a new app and fill in the needed fields. Use a redirect URI that goes to your Dynamics 365 environment.

3. Open the app you registered. Add API permissions for the Power BI service. Pick Delegated permissions and select what you need.

4. Make sure an admin gives consent for these permissions.

5. Check that your Dynamics 365 instance is active and can work with Power BI.

6. Remove any old data source permissions in Power BI Desktop if you need to.

7. Learn about your Dynamics 365 data model. Know the entities and relationships.

Note: Always think about data security and rules when you move data between systems.

Power BI Setup

Install Power BI Desktop

You must put Power BI Desktop on your computer first. Check if your computer can run it. You need a 64-bit processor. It should be at least 1.4 GHz, but 2.0 GHz is better. Your computer must use Windows 10 or Windows 11. You also need Internet Explorer 11 or something newer. The screen should show at least 1440x900 or 1600x900 pixels. You need at least 1 GB of RAM, but 4 GB is best. You also need 1 GB of free space on your hard drive. Power BI Desktop lets you connect to Dynamics 365 and other data. This setup helps you use your business data easily.

Tip: Update Power BI Desktop often. New updates fix problems and add new things.

Enable Features in Dynamics 365

You have to turn on some features in Dynamics 365 to use Power BI. Do these steps:

1. Register an app in Microsoft Entra (Azure AD) with the right API permissions for Power BI Service.

2. Turn on the PowerBI.com setting in the Dynamics 365 Finance and Operations client.

3. Find these settings in the Microsoft Entra portal under App registrations and in Dynamics 365 under the PowerBI.com configuration page in the System administration menu.

4. Set up permissions, make client secrets, and type the Application ID and secret in Dynamics 365.

5. Make sure users have the right licenses and roles.

6. Check that the domain in Microsoft Entra matches the Power BI account domain.

You need to be an Azure Active Directory admin and a System Administrator in Dynamics 365. At least one user should log in to PowerBI.com before you start. Use the same tenant for both Dynamics 365 and Power BI accounts.

Prepare Dataverse

Dataverse keeps your Dynamics 365 data safe. You can use it to make reports in Power BI. Start Power BI Desktop and pick the 'Dataverse' connector. Sign in with your user name and password to connect to the right Power Platform environment. Choose the tables you want for your report. Pick Import mode for saved data or DirectQuery for live data. Use GUID columns to link tables and keep your data correct.

You can make new metrics with Data Analysis Expressions (DAX). Right-click the entity, write your logic, and save your new measure. Add these measures to your charts. When you finish, publish your report to the Power BI service. If you want more data, use the Dataverse connector or other sources. Change and link new data if you need to. Always check for special columns like choice or lookup columns, because these can change how fast your report works.

Note: Make your queries better and only pick the data you need. This helps you stay under size limits and keeps your reports quick.

Connect Power BI

When you connect Power BI to your Dynamics 365 data, you get many ways to make reports. You can use different connectors. You can pick how to bring in your data. You can even make quick reports from your Dynamics 365 views. Each way has its own steps and best times to use it.

Choose Connector

You have a few connector choices when you link Power BI to Dynamics 365. Each connector works with data types and fields in its own way. The table below shows how they are different:

To connect, open Power BI Desktop and click "Get Data." Search for "Dynamics 365 Customer Insights" or "Dataverse." Sign in with your work account. You can pick many tables from your environment to load data. These connectors are free, but you need the right Power BI license to share and publish reports.

Tip: Use Power Query Builder or FetchXML if you want the most data and easier modeling.

Import vs. DirectQuery

When you connect Power BI to Dynamics 365, you must pick how to bring in your data. You can use Import mode or DirectQuery. Each way has good and bad points. The table below helps you compare:

Import mode puts data in Power BI. Reports run fast and you get all features. DirectQuery connects live to Dynamics 365. You always see the newest data. Use DirectQuery if your data is too big to import or you need real-time updates. Import mode is better for most reports, unless you need live data or have huge datasets.

Note: Composite models let you use both ways in one report. You can import some tables and use DirectQuery for others.

To turn on DirectQuery with the TDS endpoint for live data, follow these steps:

1. Go to the Power Platform Admin Center and pick your environment.

2. Open Settings > Features and turn on the TDS endpoint.

3. In Security Roles, give users the "Allow user to access TDS endpoint" privilege.

4. Open Power BI Desktop and click "Get Data" > "SQL Server Database."

5. Type your Dynamics 365 URL with port 5558 (like yourorg.crm.dynamics.com,5558).

6. Pick DirectQuery mode and choose your tables.

7. Build your report and publish it to Power BI Service.

8. In Power BI Service, set the dataset credentials to use OAuth2 and enforce security roles.

Visualize This View

The "Visualize This View" feature in Dynamics 365 lets you quickly make Power BI reports from any grid view. You do not need to leave Dynamics 365 or know how to build reports from scratch.

• You can pick any grid view and click "Visualize this view." Power BI will make charts and visuals from the data you see.

• The feature follows Dataverse security rules, so you only see data you are allowed to see.

• You can filter the grid before you visualize, making your report more focused.

• You can change chart types, legends, and tooltips to make your visuals your own.

• If you save the report, it goes to your Power BI workspace and uses DirectQuery with Single Sign-On. This keeps your data current and safe.

• The session for the auto-made report lasts one hour unless you save it.

• You can only export summarized data, and some tables like Activities are not supported.

• You need a Power BI Pro license to create, publish, or edit these reports.

• Sometimes, the report may show more columns than your original view, especially if there are foreign keys or hierarchy fields.

• Putting these reports into forms needs extra steps, like editing XML.

• The first report creation can be slow, and dashboards may not work well on phones.

Tip: Use "Visualize This View" for quick insights or to start building a more detailed report.

Build Reports

Data Modeling

You need a strong data model to build useful reports from Dynamics 365 data. Data modeling shapes how you connect, organize, and use your data in Power BI. Good data modeling helps you answer business questions quickly and keeps your reports running fast.

• Start by understanding your Dynamics 365 tables and how they relate. You can use relationships in Power BI to link tables. Choose between one-to-many, many-to-one, or many-to-many relationships based on your data.

• Create calculated columns and measures to add new insights. For example, you can make a measure that sums sales or counts open cases.

• Avoid common mistakes. Do not create too many relationships. Keep your tables simple. Only bring in the columns you need.

• Use clear names for tables and fields. This helps everyone understand your reports.

• Test your model with sample data before you build visuals.

Tip: A good data model makes your reports faster and easier to use. Always check your relationships and calculations before sharing your report.

Power BI Visualizations

You can turn your data into clear stories with the right visuals. Power BI gives you many ways to show your data. Pick the best visual for your business question.

1. Use drill-throughs to let users explore details. For example, start with sales by region, then drill into each city.

2. Build Power BI apps for a multi-page, app-like experience.

3. Add headlines and infographics to tell a story with your data.

4. Choose donut charts instead of pie charts for better performance.

5. Start with column charts, then use bar charts if you need to compare items.

6. Use treemaps for legends when you have many visuals with the same legend.

7. Place important visuals on the left side of the page. This helps people read your report easily.

8. Avoid horizontal scrolling. Keep all visuals in view.

9. Use simple backgrounds, like gray with white, to keep focus on your data.

10. Create custom themes for colors, fonts, and chart styles. This keeps your reports looking the same across your business.

You can also use filters, slicers, and drill-downs to make your reports interactive:

• Add slicers for key fields like Owner, Product Category, or Date Range. Slicers help users pick what data they want to see.

• Use page-level filters to focus on one part of your data. Report-level filters work across all pages.

• Field parameters let users switch between measures, like Revenue or Profit, in the same visual.

• Turn on drill-down mode in visuals. Users can click to see more detail, such as moving from Region to City.

• Build hierarchies, like Product Category > Sub-Category > Product, to help users explore data step by step.

• Try the Hierarchy Slicer. It lets users expand and collapse levels, making it easy to filter and find details.

Note: Interactive visuals help users find answers fast. Use filters and drill-downs to make your reports more powerful.

Quick Reports

You can create quick reports for single customer records in Dynamics 365. This method loads only the data you need, so your report opens fast.

1. Open Power BI and choose Get Data > Blank Query.

2. In the query source, enter a JSON URL for your Dynamics 365 table. Use the $select parameter to pick only the columns you need.

3. Use the function Json.Document(Web.Contents(URL)) to get your data.

4. Check your query and column names by opening the URL in a browser with your login.

5. When Power BI shows a List, right-click and choose 'To Table'.

6. Rename the column to something clear.

7. Click the double-arrow icon to expand records into columns. Accept the default settings.

8. Change column types if needed, like turning text into numbers or dates.

9. Rename your query to match the data, such as "CustomerRecord".

10. Click Close & Apply to load your data into Power BI.

This approach lets you build focused reports for each customer. You only load the data you need, so your reports stay quick and easy to use.

Tip: Use quick reports for customer meetings or when you need to see details for one record without waiting for a large report to load.

Embed and Share

Add to Dashboards

You can add your reports to dashboards in Dynamics 365 to help your team see important data in one place. Follow these steps to pin a report:

1. Choose a view in Dynamics 365, like recent opportunities. Make sure it has the fields you need.

2. Use the 'Visualize This View' button to create a quick visualization.

3. Save the visualization as a report in your workspace. You need a Power BI Pro license to do this.

4. Pin the report to a dashboard. You can make a new dashboard, such as "Revenue Opportunity Dashboard."

5. Go to the dashboard section in Dynamics 365. Create a new dashboard, select "Power BI Dashboard," and pick your workspace and dashboard.

This setup lets you see real-time updates. You also get single sign-on, so you do not have to log in again.

Tip: Pinning reports to dashboards helps your team track key metrics easily.

Embed in Apps

You can embed reports directly into Dynamics 365 apps. This makes it easy for users to see data while working.

• Use Power BI Embedded to add reports as part of your app deployment.

• You can support drill-down, contextual filtering, and direct navigation to report tabs.

• Role-based security keeps your data safe.

• You can customize reports using Power BI Desktop before embedding.

• Filters can show data based on the user's context.

Another way is to publish your report to the web and use an iFrame in a Dynamics 365 form. Add a new tab or section, insert the iFrame, and paste the report URL. Remember, this method makes the report public, so use it only for non-sensitive data.

Manage Access

You must manage who can see and use your embedded reports. Here are some best practices:

• Make sure users have the right licenses.

• Set permissions and authentication for embedded reports.

• Use embed tokens for secure access.

• Review permissions in both Dynamics 365 and Power BI often.

• Use audit logs to track who views dashboards.

• Train users on how to use embedded reports.

• Monitor reports and update them as needed.

Note: Regular checks help keep your data secure and your reports running smoothly.

Troubleshooting

Connection Issues

Sometimes, you might have trouble connecting your reports to Dynamics 365. These problems can stop your data from loading or updating. Here are some common issues and ways to fix them:

• Gateway connection problems can block your access. Try restarting the gateway service to fix it.

• Wrong username or password often causes errors. Check your login details and update them if needed.

• If you see database access errors, you may not have permission. Make sure you have the right access in both places.

• Problems like Kerberos errors can stop data from loading. Set up Service Principal Names and delegation in Active Directory.

• If some settings do not match, you can have trouble. Turn on all needed attributes and use user name mapping for login.

Tip: Test your connection after you make changes. This helps you find problems early.

Performance Tips

Big datasets can make your reports slow. You can follow these steps to keep your reports fast and easy to use:

1. Set your capacity settings to fit your needs, especially with Premium or Embedded.

2. Make sure your data gateways are the right size for your work.

3. Keep your data sources, gateways, and report servers close together to lower network delays.

4. Use tools like Azure Speed Test to check your network speed.

5. Watch your report and query speed while building and after publishing.

6. Build your data model with a star schema for better speed.

7. Filter and group your data before importing to keep it small.

8. Pick simple data types, like integers, to save memory.

9. Put seven or fewer visuals on each page.

10. Use simple visuals, like bar or line charts.

11. Set data refresh times during off-peak hours.

12. Use incremental refresh to update only new or changed data.

Note: Check your report speed often with the Performance Analyzer tool.

Security

It is important to keep your data safe. You need to make sure only the right people can see the right data. Here are some steps you can follow:

• Dynamics 365 uses record-level security. Users only see their own data, like their sales or cases.

• If you publish a report as an admin, everyone might see all data. Use Row-Level Security to show each user only their data.

• Test your Row-Level Security roles to make sure users see only what they should.

• Turn off 'publish to web' to stop public sharing.

• Add sensitivity labels to your reports to control sharing and exporting.

• Set rules for who can access data, how long to keep it, and train users.

• Keep your data gateways safe and up to date.

• Encrypt your data when it is stored and when it is sent.

Tip: Learn your company’s security rules before you share any reports.

---

You can link Power BI with Dynamics 365 by doing these steps: First, make sure your accounts and permissions are ready. Next, connect to your Dynamics 365 data. Then, bring in your data and set it up. After that, create and share your reports. You can also share dashboards and set up refresh times. Try both fast and detailed ways to do this. Always check your security and make your data run faster. If you want to learn more, look at Microsoft Learn, Power BI Community, or get advanced certificates.

FAQ

How do you refresh Power BI reports with new Dynamics 365 data?

You can set up scheduled refresh in the Power BI service. Go to your dataset settings, then choose how often you want Power BI to pull new data from Dynamics 365.

Can you use Power BI with Dynamics 365 if you have no coding skills?

Yes, you can. Power BI offers drag-and-drop tools and easy connectors. You do not need to write code to build basic reports or use "Visualize This View."

What should you do if a Power BI report loads slowly?

Try these steps:

• Remove unused columns and tables.

• Use filters to limit data.

• Choose simple visuals.

• Check your internet speed.

Is it safe to share Power BI reports with others?

You can share reports safely if you set up Row-Level Security and use the right permissions. Always check who can view or edit your reports before sharing.