Incorrect DAX Query Generation for Export in Embedded Power BI Reports

[aniket_007]

Hello everyone,

I'm currently working on a use case where I have to export the tabular report data from an embedded Power BI report.
But currently, we are unable to generate correct DAX queries to export the exact column data that is currently visible in the embedded Power BI report.

The goal is to export only the columns and rows that are visible to the user in the report view.
I’d appreciate your feedback on the current methods I’ve tried and would love to hear if there are better alternatives.

Problem Statement
We need to export only the columns currently visible in an embedded Power BI report, but the generated DAX query does not match the actual report view. It includes extra columns, misses visible ones, and does not correctly capture slicer selections.

Current Approach

-> Using report.getFilters() to get the filter data from the report.
-> Using visual.getSlicerState() for slicer visuals.
-> Using extractSchemaFromReport() for the report metadata.

Issues Observed:

1. Slicer Issue: When the Power BI report initially loads, certain slicers are pre-populated with default values. These predefined selections are not being captured in our DAX query during the initial load. However, when users manually change or select slicer values after the report has loaded, the updated selections are correctly captured in the getSlicerState().

2. Column Mismatch: Using getFilters() and getSlicerState() we're unable to identify the exact columns visible to the user in the grid. Specially the columns which can be dynamically added or removed using the slicers built into the report.

I had couple of questions which are as follows:

-> Is there any better approach to generate a DAX query dynamically using the data recieved from embedded report operations?
-> How can we identify visible columns to the user in the embedded report.
-> How to capture slicer and filter values when the report loads as we are not getting this information using embedded report operations.

Thanks in advance,
Aniket

[v-achippa]

Hi @aniket_007,

Thank you for reaching out to Microsoft Fabric Community.

Currently power bi Embedded APIs do not expose visual level DAX queries, default slicer states or visible column state, this is a known limitation of Power BI Embedded.

If the requirement is to export exactly what the user sees, the only supported way is to use power bi’s built in export capabilities. Dynamically reconstructing visual queries using Embedded APIs will be partial and may not match the rendered output.

If this capability is critical, please consider submitting your detailed feedback and ideas through Microsoft's official feedback channels. Feedback submitted through these channels is frequently reviewed by the product teams and can contribute to meaningful improvements.

Fabric Ideas - Microsoft Fabric Community

Thanks and regards,

Anjan Kumar Chippa

[deepa_deshpande]

Thanks Anjan Kumar. The default export functionality of Power BI is not good enough for our reports because many of the reports need to export more than 150K rows. 

In our organization, Report Builder is not allowed.

Power Automate scripting will not work either: Due to the dynamic selection of the fields (using field parameters), users can select any set of fields. Writing a Power Automate script for such use case is becoming very complex. 

so we are looking for the best recommended approach. 

Is there any way to extract (not generate) the DAX query that the report uses?

[lbendlin]

Is there any way to extract (not generate) the DAX query that the report uses?

Reports don't use DAX queries. Report visuals do use DAX queries. In a standard environment you can see the DAX query for each visual by using the "Export... Live connection" option and then examining the table query. But this may not be possible in an embedded scenario, and it is a manual process anyway.

[v-achippa]

Hi @aniket_007,

Thank you @lbendlin for the response.

Currently there is no supported way to extract the DAX query that the report uses. If this capability is critical, please submit feedback through Microsoft’s official feedback channels.

Thanks and regards,

Anjan Kumar Chippa

[deepa_deshpande]

There is no solution so far that works for all the reports - specifically those that are complex. so we need the 'best approach' that would work

[pslabdesh]

Hi aniket did you find any solution to this so far. Ive faced similar issue in the past.