Why Underlying Data Export Fails in Power BI Embedded but Works in Service: Deep Dive & Fixes

by

Exporting data from visuals in Power BI is a critical capability for analytics consumers. While Power BI Service usually supports the export of both summarized and underlying data, organizations embedding reports using the Power BI JavaScript API often face issues—especially with underlying data export. In this article, we explore a specific case:

Issue: “Underlying data” export fails in Power BI Embedded for some reports, while the same reports allow exports successfully in the Power BI Service.

We break down the technical reasons behind this behavior and offer verified solutions.

✅ Scenario at a Glance

The issue typically appears under these conditions:

  • Reports are embedded using the Power BI JavaScript API.

  • Visuals are table visuals based on DirectQuery.

  • The dataset (semantic model) resides in another workspace (shared dataset).

  • Export of underlying data fails, while summarized/full data export works.

  • The same report works fine in the Power BI Service.

🚫 Core Limitations Causing Export Failures

Here’s a deep dive into the technical constraints and limitations affecting Power BI Embedded’s ability to export underlying data:

1. Semantic Model from Another Workspace

  • Reports that use a shared dataset across workspaces (a thin report model) rely on DirectQuery to a Power BI dataset.

  • Limitation: Power BI Embedded does not support underlying data export when the dataset is connected via DirectQuery to another dataset using a service principal.

  • ➤ This limitation is documented by Microsoft and cannot be bypassed unless using “user owns data” embedding.

🔗 Microsoft Docs – Export API limitations

2. DirectQuery Data Size and Timeout Limits

  • Power BI limits DirectQuery-based exports to 16 MB uncompressed.

  • Even if the visual has fewer rows, large column widths or complex data types can exceed this limit.

  • Visuals pulling high-cardinality data or broad fact tables can easily breach this limit and silently fail.

3. Missing Unique Keys in the Model

  • Underlying data export requires that every table used in a visual has a unique key.

  • If the model lacks proper 1:n relationships or relies on many-to-many joins, Power BI cannot flatten the data for underlying export.

4. Unsupported Visual Settings

  • Certain configurations block export:

    • “Show items with no data” enabled.

    • Use of measures instead of basic aggregates (e.g., SUM, COUNT).

    • Custom visuals like R, Python, or third-party visuals.

Even a table visual can fail if it uses complex measures or visual-level filters that conflict with export logic.

5. Embed Token and Permissions

  • The embed token must include:

    • reportPermissions: "Export"

    • Explicit access to the dataset ID (especially if the dataset is in another workspace).

  • If the service principal lacks access to the dataset directly, export will silently fail even if the report renders.

6. Row-Level Security (RLS)

  • RLS may filter out all rows for the embed context.

  • While your admin account in Power BI Service bypasses RLS, the service principal might see nothing—causing export to fail or return empty CSVs.

7. SSO and datasourceIdentity

  • If you’re using SSO with on-premises data gateways, and the embed token includes datasourceIdentity, export of underlying data is blocked by design in embedded scenarios.

🧪 Diagnosis Techniques

Here’s how to identify the exact failure cause:

🔍 Use JavaScript Debugging

From the browser dev console:

js
report.getPages().then(pages => {
pages[0].getVisuals().then(visuals => {
visuals[0].exportData(models.ExportDataType.Underlying, 1000)
.then(data => console.log("Export URL:", data.url))
.catch(error => console.error("Export Error:", error));
});
});

  • Check for errors like:
    "Export is not allowed due to security settings"
    "Visual type is not supported for export"
    "Underlying data is not available for this visual"

🛠 Solutions & Workarounds

Here’s what you can do to fix or work around this issue:

✅ 1. Move Dataset to the Same Workspace

  • Keep the report and dataset in the same workspace.

  • Avoid cross-workspace DirectQuery to other semantic models.

  • This resolves the embed API limitation tied to composite models.

✅ 2. Use Import Mode (If Feasible)

  • If DirectQuery isn’t a hard requirement, switch to Import mode.

  • This removes size/time constraints and supports full export features.

✅ 3. Simplify the Visual

  • Use only columns with simple aggregations.

  • Avoid complex measures and “Show items with no data”.

  • Create a simplified hidden table visual strictly for export.

✅ 4. Embed with User Identity (Not Service Principal)

  • Use user authentication for embedding (“user owns data”) instead of a service principal.

  • The limitation on underlying export applies only to service principal + composite model combo.

✅ 5. Pre-filter the Data

  • Apply report-level or visual filters to limit the exported data volume under the 16 MB threshold.

✅ 6. Consider Paginated Reports

  • For guaranteed export of large detailed datasets, use Paginated Reports instead of interactive visuals.

  • These are designed specifically for detailed tabular exports.

🧠 Summary Table

Cause Applies in Service? Affects Embed? Fix
Cross-workspace semantic model ❌ ✅ Move dataset to same workspace
DirectQuery size limit ⚠️ Sometimes ✅ Filter data, use Import mode
No unique key in table ❌ ✅ Redesign model schema
Complex measure in visual ❌ ✅ Use basic aggregations
Service principal without dataset access ❌ ✅ Update embed token access
SSO with datasourceIdentity ❌ ✅ Avoid SSO in embed context
RLS filters out rows ⚠️ ✅ Test without RLS
Paginated report alternative ✅ ✅ Use for large exports

📚 Helpful Links

Final Thoughts

Export functionality is critical if you embed Power BI reports for your customers or internal users. The Power BI JavaScript API supports summarized and underlying data, but not in every scenario.

If you’re facing this issue:

  • Focus on avoiding composite models with service principals,

  • Simplify visuals,

  • Or explore Paginated Reports.

Leave a Comment