Power BI + Dynamics 365: Native Integration Deep Dive

No BI integration in the Microsoft ecosystem is tighter than Power BI and Dynamics 365. Both products share the same cloud infrastructure, authentication model, and data platform (Dataverse). Microsoft has invested heavily in making this integration seamless — to the point where embedded Power BI reports appear natively within Dynamics 365 forms, dashboards, and views with no separate login required.

Yet many Dynamics 365 organizations only scratch the surface of this integration. This guide covers every integration pattern from basic Dataverse connection to embedded reports, Dynamics 365 Sales analytics, Finance dashboard design, and cross-module analysis across CRM, ERP, and Field Service.

Key Takeaways

• Dataverse connector is the native, preferred connection method for all Dynamics 365 data
• Embedded Power BI reports appear inside D365 forms and dashboards without leaving the UI
• Microsoft Fabric and Dataverse share the same Azure infrastructure — Fabric shortcuts eliminate data movement
• Dynamics 365 Sales has a native analytics app (Power BI template app) deployable in minutes
• Finance and Operations uses Azure Data Lake integration for large-scale analytics
• Cross-module analytics (Sales + Finance, Service + Inventory) require joining Dataverse tables with Finance data
• Row-level security mirrors Dynamics 365 business units and security roles
• Power BI paginated reports integrate with D365 Finance for regulatory/statutory reporting

---

Dynamics 365 Products and Their Data Sources

Understanding which Dynamics 365 product uses which data platform is essential for integration design:

Key distinction: Dynamics 365 apps built on Dataverse (Sales, Service, Field Service, Marketing) use the Dataverse connector. Dynamics 365 Finance & Operations uses separate cloud-hosted Azure SQL databases with a dedicated Power BI connector and Azure Data Lake export.

---

Connector 1: Dataverse (Primary Connector)

The Dataverse connector is the recommended integration path for all Dataverse-based D365 apps. It provides:

• Standard tables: All D365 standard entities (Accounts, Contacts, Opportunities, Cases, etc.)
• Custom tables: Any custom tables created by your organization
• Choice columns: Lookup values with labels (not just internal codes)
• Relationships: Navigatable via Power Query relationship expansion
• Real-time data: No data movement — queries go directly to Dataverse

Setup

1. In Power BI Desktop: Get Data → Dataverse
2. Environment URL: https://{your-environment}.crm.dynamics.com
3. Sign in with your Dynamics 365 credentials (or service principal for scheduled refresh)
4. Browse tables and select required entities

Key Tables for D365 Sales Analytics

account           — Customer/Prospect organizations
contact           — Individual contacts
opportunity       — Sales deals with stage, probability, close date
quote             — Formal price quotes linked to opportunities
salesorder        — Confirmed sales orders
invoice           — Invoices from orders
lead              — Incoming leads pre-qualification
activitypointer   — All activities (emails, calls, meetings)
systemuser        — Sales reps and users
team              — Sales teams and territories

Handling Dataverse Option Sets

Dataverse option sets (choice columns) store integer codes internally. The Dataverse connector returns both the code and label, but you must handle this in Power Query:

// Expand opportunity status code to label
Source = Dataverse.EntitySet("https://org.crm.dynamics.com", "opportunity"),
AddLabel = Table.AddColumn(Source, "Stage Label",
    each if [statuscode] = 1 then "In Progress"
    else if [statuscode] = 2 then "Won"
    else if [statuscode] = 3 then "Cancelled"
    else "Unknown")

Better practice: Use the [email protected] field which Dataverse returns automatically with formatted labels.

---

Connector 2: Dynamics 365 Finance & Operations

D365 Finance and Operations uses a separate connector that connects to the Finance virtual entity layer or directly to Azure Data Lake (recommended for large-scale analytics).

Option A: Finance & Operations Connector (Entity Store)

Power BI's Dynamics 365 Finance connector queries the Finance Entity Store — a separate read-optimized database updated by Finance's batch processes.

1. Get Data → Dynamics 365 (online) → Finance
2. Environment URL: https://{env}.operations.dynamics.com
3. Connect to Entity Store cubes and data entities

Available entities (examples):

• RetailSalesLine — Sales transaction details
• VendTransaction — Vendor payment transactions
• CustTransaction — Customer transactions
• LedgerTransactionEntity — General ledger entries
• BudgetTransactionEntity — Budget entries

Option B: Azure Data Lake Export (Recommended)

For large-scale Finance analytics (10M+ transactions), configure Finance's "Export to Azure Data Lake" feature:

1. In Finance & Operations admin: Workspace → Data Management → Configure Azure Data Lake
2. Connect to your Azure Data Lake Storage Gen2 account
3. Select tables to export (incremental or full)
4. Power BI reads directly from ADLS via Azure Data Lake Storage connector

This architecture handles Finance data at any scale without impacting the Finance application's performance.

Option C: Microsoft Fabric (Recommended for New Deployments)

With Microsoft Fabric, D365 Finance data in ADLS becomes a Fabric shortcut — accessible to Power BI via Direct Lake mode without additional data movement:

D365 Finance → ADLS Gen2 (via Export) → Fabric OneLake (Shortcut) → Power BI Direct Lake

No ETL pipeline required. Fabric reads ADLS data as if it were native OneLake content.

---

Embedded Power BI Reports in Dynamics 365

This is one of Power BI's most valuable Dynamics 365 integration features — reports embedded directly within D365 forms and dashboards.

Setting Up Embedded Reports

1. Publish the Power BI report to a Premium or PPU workspace (free users need Premium capacity for viewing)
2. In Dynamics 365 Settings: Administration → System Settings → Reporting tab
3. Enable: "Visualizations in Power BI"
4. On any D365 dashboard: Add component → Power BI Tile → Select your report/dashboard
5. On D365 forms: Open form editor → Insert → Sub-Grid or Web Resource → PowerBI

Context Filtering (Report Filters from D365 Record)

The most powerful embedded scenario: a Power BI report that automatically filters to the current D365 record.

For example, opening a D365 Account record shows an embedded Power BI report showing only that account's revenue history, open cases, and pipeline — no filtering needed by the user.

Configure via JavaScript web resource that passes the current record's GUID to the Power BI report filter:

// D365 form JavaScript — pass account ID to Power BI filter
function passRecordToReport(executionContext) {
    const formContext = executionContext.getFormContext();
    const accountId = formContext.data.entity.getId();

    // The Power BI report uses this parameter for row filtering
    const filterValue = accountId.replace(/[{}]/g, '');

    // Update the Power BI visual's filter via PostMessage
    const powerBiFrame = document.getElementById('powerBiReport');
    powerBiFrame.contentWindow.postMessage(
        JSON.stringify({ action: 'filter', accountId: filterValue }),
        'https://app.powerbi.com'
    );
}

---

Cross-Module Analytics: Sales + Finance + Service

The true analytical power of D365 + Power BI emerges when you join data across modules. Here are key cross-module analytics patterns:

Customer 360 Dashboard

Join Dataverse (Sales, Service) with Finance data:

// Customer Health Score (composite measure)
Customer Health Score =
VAR PaymentScore = SWITCH(TRUE(),
    [Avg Days to Pay] <= 30, 40,
    [Avg Days to Pay] <= 60, 25,
    [Avg Days to Pay] <= 90, 10,
    0
)
VAR SupportScore = SWITCH(TRUE(),
    [Open Cases] = 0, 30,
    [Open Cases] <= 2, 20,
    [Open Cases] <= 5, 10,
    0
)
VAR PipelineScore = IF([Open Pipeline Value] > 10000, 30, 15)
RETURN PaymentScore + SupportScore + PipelineScore

Sales-to-Cash Analytics

Track the journey from opportunity to cash receipt:

Lead → Opportunity → Quote → Order → Invoice → Payment

DAX measures for stage conversion:

// Lead-to-Opportunity Conversion Rate
Lead to Opp Rate =
DIVIDE(
    CALCULATE(COUNTROWS(Lead), Lead[statuscode] = 3),  -- 3 = Qualified
    COUNTROWS(Lead),
    0
)

// Quote-to-Order Conversion Rate
Quote Win Rate =
DIVIDE(
    CALCULATE(COUNTROWS(Quote), Quote[statecode] = 1),  -- 1 = Won
    COUNTROWS(Quote),
    0
)

// Average Days from Opportunity to Invoice
Avg Sales Cycle Days =
AVERAGEX(
    FILTER(Opportunity, Opportunity[statecode] = 1),  -- Won
    DATEDIFF(Opportunity[createdon], Opportunity[actualclosedate], DAY)
)

---

Security: Mirroring D365 Security Roles

Dynamics 365 uses a sophisticated security model: Business Units, Security Roles, Teams, and Field-Level Security. Power BI's RLS can mirror this model.

Business Unit-Based RLS

// RLS filter — restrict data to user's business unit hierarchy
[OwningBusinessUnit] IN
    PATHCONTAINS(BusinessUnit[BusinessUnitPath],
        LOOKUPVALUE(
            SystemUser[BusinessUnitId],
            SystemUser[Email],
            USERPRINCIPALNAME()
        )
    )

Salesperson-Level Data Isolation

// Opportunities visible to current user (their own + manager's team)
CALCULATE(
    [Total Pipeline Value],
    FILTER(
        Opportunity,
        Opportunity[OwnerEmail] = USERPRINCIPALNAME() ||
        Opportunity[OwnerManagerEmail] = USERPRINCIPALNAME()
    )
)

For complex D365 security hierarchies, synchronize the D365 security role assignments to a Power BI dimension table daily, then apply RLS based on that mapping.

---

D365 Finance Dashboards

CFO Financial Dashboard KPIs

// Accounts Receivable Turnover
AR Turnover =
DIVIDE([Total Revenue], AVERAGE(Balance_Sheet[AR_Balance]))

// Days Payable Outstanding
DPO =
DIVIDE(
    CALCULATE(SUM(VendTransaction[AmountCur]), VendTransaction[TransType] = "Purch"),
    DIVIDE([Total COGS], 365),
    0
)

// Working Capital
Working Capital =
[Total Current Assets] - [Total Current Liabilities]

// Operating Cash Flow (indirect method)
Operating Cash Flow =
[Net Income] +
[Depreciation & Amortization] +
[Change in Working Capital]

---

Frequently Asked Questions

Does Power BI work with Dynamics 365 Business Central?

Yes — Microsoft provides a dedicated Business Central connector for Power BI. Business Central includes embedded Power BI reports in its Role Center pages by default. The connector uses OData APIs and exposes Business Central's API pages (financial entries, items, customers, vendors). Refreshing Business Central data requires a Power BI Pro or Premium license and supports up to 48 refreshes per day on Premium.

Can I use DirectQuery with Dataverse?

Yes — the Dataverse connector supports DirectQuery mode. Dataverse queries execute against the Dataverse service in real-time. For most D365 sales and service analytics (millions of records), DirectQuery on Dataverse performs well. For very large Dataverse environments (100M+ rows), consider importing data to a Fabric lakehouse for better performance.

How do I handle Dynamics 365 security in Power BI for external users?

External users (customers, partners) accessing embedded Power BI reports need special handling. Options include: Azure AD B2B guest accounts with Power BI Pro licenses, or Power BI Embedded (A SKU) with service principal authentication and row-level security enforced by your application. Never expose unfiltered D365 data in embedded scenarios — always apply strict RLS to isolate each external user's data.

What is the Dynamics 365 Sales Insights integration with Power BI?

Dynamics 365 Sales Insights (Viva Sales) generates predictive analytics (opportunity scoring, relationship health, pipeline forecasting) natively within D365. This data is stored in Dataverse and can be connected to Power BI for custom visualization. Power BI can enhance Sales Insights data with external benchmarks, historical trend analysis, and cross-module correlations not available in D365's native analytics.

Can Power BI replace Dynamics 365's built-in dashboards?

Power BI significantly extends what D365's built-in dashboards can do — custom visualizations, complex DAX measures, cross-module analytics, and paginated reports are all unavailable in D365's native chart editor. However, D365 dashboards remain useful for day-to-day operational views within the application. The recommended approach is: D365 native dashboards for quick operational views, Power BI for analytical and executive reporting.

---

Next Steps

The Power BI + Dynamics 365 integration is the most native, feature-rich BI integration in the enterprise software market. Organizations that invest in this integration gain unified visibility across CRM, ERP, and Service operations that was previously impossible without custom development.

ECOSIRE's Power BI team specializes in Dynamics 365 analytics — from simple embedded dashboards to complex cross-module Customer 360 and Sales-to-Cash analytics. We bring both Power BI and Dynamics 365 expertise to ensure the integration is secure, performant, and aligned with your D365 security model.

Explore our Power BI ERP integration services for Dynamics 365 implementation details, or contact our analytics team to design a Power BI architecture that maximizes your Dynamics 365 investment.