Managing and Exporting High-Volume Datasets (~1M Records) in Power BI

Hi Sidhant 

Both of the approaches you’ve tried are valid directions, but each comes with its own limitations that explain the issues you’re facing. With the paginated report route, simply using visibility rules on columns often fails because while the report view might hide them, many export renderers (especially Excel and CSV) still output all columns regardless of visibility, which is why users keep seeing everything; the more reliable method is to build the dataset dynamically (for example through a stored procedure that returns only the selected columns) so that the export itself contains exactly what was chosen. On the Fabric side, your “Bad Request” errors usually stem from mismatched authentication or payload—Fabric APIs require a proper Azure AD token and very specific endpoint formatting, which Power Automate doesn’t automatically handle unless you set up a service principal or OAuth flow. Best practice for handling 1M+ rows is to avoid pushing them through Power BI visuals or standard exports at all: instead, use paginated reports with dynamic datasets if you need user-driven exports, or better yet, trigger a Fabric pipeline that writes the selected data to OneLake/ADLS in Parquet/CSV and then share the link or notify the user via Power Automate. In short, paginated reports are fine for “ad-hoc but smaller” exports when you control the dataset, while Fabric pipelines are the scalable option for very large datasets; both require careful parameter handling and correct authentication, and often the cleanest pattern is a hybrid—parameters from Power BI or Power Automate passed into a Fabric pipeline that generates the extract on demand.

Hi folks,

Thanks again for all the suggestions shared so far  they've been very helpful. However, due to several constraints in our environment (as mentioned earlier by SantoshPothnak  and myself), many of the proposed solutions aren’t feasible for us. To quickly summarize:

• End-user focus: The export functionality is intended for external end users via an embedded Power BI report, so options like Analyze in Excel, translytical flows, and B2B sharing are not viable.

• Tooling restrictions: Use of tools like Fabric Notebooks or external tools such as DAX Studio and Bravo is also not permitted in the current setup either due to environment policies or because they aren’t suited for end users.

Based on internal discussions and feedback from the team working on the implementation, the current acceptable path forward looks like this:

• Provide a button or link within the embedded report to allow users to initiate a CSV export.

• The exported file would ideally reflect filtered data (based on the report state), but even if that’s not possible, a default dataset dump is acceptable.

• The file can be stored at a shared location where users can download it once it’s generated.

• It’s understood that since this is a CSV export, formatting and formulas won’t be retained.

Also, we've learned that other teams working in similar constraints have gone with a parallel, report-independent export flow — for example, triggering data exports via stored procedures on Synapse using custom code in a portal. While we don't have full visibility into those implementations, it's reassuring to know this is a path others have taken successfully.

We’re continuing to explore the best way to build this within the current limits — happy to hear if anyone has optimized such an approach further.

v-hashadapu , Gabry , Poojara_D12  if you guys have any inputs please do share them (for the path that is mentioned on how to achieve the same if you have any relevant documents/videos or articles that can please do let me know)

Regards,
Sidhant.

I've never tried this myself, but I was thinking, if you're using Fabric, one possible solution could be the following:

You can set up a report with a column filter. Then, using a user data function, you can trigger a notebook that receives the selected columns as input. The notebook can export the filtered data and save it to a folder within the lakehouse. Users can then access the data directly from that folder.

Could this be a viable solution?

Hi Sidhant , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

Hi v-hashadapu , Gabry ,
I had another query by any chance is there any way through which we can increase the 150K (export limit), like for a specific usecase by connecting with the Microsoft Team and increase the limit like how we have in AWS let's say we want to increase some default limit we can easily increase that or in case of Azure Blob storage the default limit {in terms of storage} is 5 PiB which can be increased by contacting the support team (Azure support), similarly can we do that (for a special requirement) {considering if my org is Microsoft Partner}.
Meanwhile I'm also implementing few workarounds, which are in-progress will share those as well (the respective blockers associated with them).

Regards,
Sidhant.

Hi v-hashadapu, Gabry 

This end-to-end solution enables users to dynamically select fields in Power BI and export the filtered data to a well-formatted Excel file via Microsoft Fabric, with automation powered by Power Automate.

The flow begins with a Power BI report where users choose specific columns using slicers or parameters. A Power Automate flow—triggered via HTTP or a Power BI button—captures these selections, along with export metadata like user email, export ID, and record limits. The flow authenticates with Microsoft Fabric using an Azure App Registration and securely triggers a Fabric data pipeline.

Inside Fabric, the pipeline filters and exports the selected data to Excel, formats the output using a notebook (with headers styled, column widths adjusted, and summary metadata added), and stores it in a Lakehouse location. After processing, Power Automate fetches the file and emails it directly to the user, attaching the Excel file with all selected data.

This workflow is scalable, secure, and user-friendly—ideal for automated reporting, scheduled exports, or on-demand sharing. It eliminates manual data exports and delivers polished Excel reports with just one click from Power BI.

This approach is possible but its costly to the user and we need lot of field parameters and slicers for example ,PFB screenshot 

So im using run a query against dax to get the dynamic parameters but im facing an issue where i need to implement the slicers and right now im working with a sample dataset with 3 field parameters and a slicer when im using perfomance analyzer i'm seeing the following DAX - 

Can anyone pleas help me on how to make the DAX more dynamic and satisfy our requirment of exporting 1m rows too.

Thankyou

Hi Sidhant , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

Hi Gabry, v-hashadapu ,
So I was going through some posts I did come across one video wherein they have demonstrated how to export more records (more than the limit). In the video the instructor mentioned 3 approaches:
i) Method-1: Using the DAX query view (in-built in Power BI Desktop) wherein we simply use: EVALUATE 'table_name' and run the DAX query and copy the data into a csv/excel

Con: The limitation is it only supports upto 500K records, beyond that not possible.

The next two approaches that were been discussed were using External Tools:
ii) Bravo

iii) DAX Studio

In both these tools all we need to do is simply select the table, the export type (excel or csv) and done.
This is better, but now for the end-user to simplify this process can we do something like within a button click (let's say we select the table) and start the export process (by calling any of the external tool explicitly).
Considering the end-user to be a non-technical person they want this process to be simplified like few clicks.

If you have any inputs with respect to this, please do let me know.

Regards,
Sidhant.

Hi Sidhant , Thank you for reaching out to the Microsoft Fabric Community Forum.

For the Paginated Report issue, the trick is to pass a single text parameter with all selected columns and then use that in the RDL column visibility expression. That prevents the all columns showing problem you mentioned.

On the Fabric + Power Automate side, Bad Request almost always points to the request body or authentication. I’d suggest testing the same call in Postman first, once it works there, copy the request into Power Automate. Pay special attention to the JSON structure and whether your service principal actually has contributor rights on the workspace.

For making exports user-friendly, instead of asking users to run external tools, you can give them a Power BI button linked to Power Automate. That button passes their selections into a Fabric pipeline or notebook, which generates the file in OneLake/Blob. The flow can then email them the file link or the file itself. That hides the complexity and keeps it to a couple of clicks for them.

Email subscriptions in Power BI don’t solve the row-limit issue, they’ll still be capped. But you can repurpose the idea by instead of subscriptions from the service, use your Power Automate flow to deliver exports on a schedule or on-demand, which gives the same experience without the limit.

Hi Sidhant , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.