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

[ShubhaGampa11]

 

Hello @Everyone ,

I'm currently working on a use case where I need to manage and export high-volume datasets (~1 million records) using Power BI, and I'm exploring multiple approaches. I’d appreciate your feedback on the current methods I’ve tried and would love to hear if there are better alternatives or optimizations.

Problem Statement:
Effectively manage and export large datasets (~1M records) in Power BI, while allowing users to select/deselect fields dynamically and ensuring smooth integration with Power Automate and Run a query against DAX

Select/deselect fields dynamically using field parameters
Filter data using slicers (e.g., date ranges)
Export the filtered and shaped data via Power Automate
Run a DAX query dynamically based on these selections
🧪 Current Approach
✅ Approach 1: Field Parameters + Power Automate
I started with a relatively simple setup:

Field Parameters in Power BI allow users to choose which columns to export.

A Power Automate button triggers a flow that receives selected fields and constructs a DAX query.
The DAX query uses SELECTCOLUMNS and FILTER to extract the required data.
Here’s a simplified version of the DAX logic:

EVALUATE
SELECTCOLUMNS(
FILTER(
'bankdataset',
'bankdataset'[Index] >= MinRows &&
'bankdataset'[Index] <= IncrRows
),
"Index", 'bankdataset'[Index],
<dynamic fields from Compose_CleanedR>
)
ORDER BY [Index]

This works well for one field parameter.

🔄 Scaling Up: Multiple Field Parameters + Slicers
The real-world use case is more complex. I now need to handle:

But i'm first trying to handle Three field parameters (e.g., Orders, People, Returns)
A date slicer for filtering by Order Date
Exporting the result via Power Automate
Here’s what the Performance Analyzer shows for the generated DAX:

DEFINE
VAR __DS0FilterTable = TREATAS({"'Orders'[Order ID]"}, 'OrderParameter'[OrderParameter Fields])
VAR __DS0FilterTable2 = TREATAS({"'People'[Region]"}, 'PeopleParameter'[PeopleParameter Fields])
VAR __DS0FilterTable3 = TREATAS({"'Returns2'[Returned]"}, 'ReturnsParameter'[ReturnsParameter Fields])
VAR __DS0FilterTable4 = FILTER(
KEEPFILTERS(VALUES('Orders'[Order Date])),
'Orders'[Order Date] >= DATE(2022, 1, 20)
)

VAR __DS0Core = SUMMARIZECOLUMNS(
'LocalDateTable'[Year], 'LocalDateTable'[Quarter], ...
__DS0FilterTable, __DS0FilterTable2, __DS0FilterTable3, __DS0FilterTable4,
"SelectedFieldsOrder", 'FieldParamTextOrder'[SelectedFieldsOrder],
"SelectedFieldPeople", 'FieldParamTextPeople'[SelectedFieldPeople],
"SelectedFieldsReturns", 'FieldsParamTextReturns'[SelectedFieldsReturns]
)

VAR __DS0BodyLimited = TOPN(1002, __DS0Core, ...)
EVALUATE __DS0BodyLimited
ORDER BY ...

Show more lines
❌ Challenges Faced
Dynamic DAX editing becomes difficult when multiple field parameters are involved.
Slicer values (like Order Date) are not easily passed into the Power Automate flow.
The generated DAX is not easily customizable for export logic.
💡 Looking for Suggestions
If you've tackled similar challenges, I’d love to hear and kindly guide me with any suggestions you have.

[v-prasare]

Hi @ShubhaGampa11 

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-dineshya · Accepted solution]

Hi @ShubhaGampa11 ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below things.

1. Use a centralized DAX template in Power Automate and dynamically inject field names using Compose actions. Consider using UNION or ADDCOLUMNS to merge multiple field parameter outputs if they belong to different tables. Use calculated tables in Power BI to pre-shape data based on slicer selections and field parameters, reducing complexity in the export logic.

2. Use DAX measures or calculated columns to capture slicer values and expose them in a table visual. In Power Automate, use “Export data” from a visual that includes these slicer-bound values. Alternatively, use Power BI REST API to run DAX queries with parameters passed from slicers.

3. Exporting Large Volumes , Paginate the export using TOPN and SKIP logic in DAX, and loop through pages in Power Automate. Use Azure Data Lake or Synapse as a staging area if you are on premium capacity. Consider DirectQuery or Hybrid Tables to offload data processing to the source system.

4. Use Performance Analyzer to identify bottlenecks and optimize visuals. Avoid using too many visuals with dynamic fields; instead, use tables with conditional formatting. Use aggregated tables or summary views for initial filtering before exporting detailed data.

5. Paginated Reports handle large exports better. You can embed them in Power BI and pass parameters from slicers.

6. Use Power Automate to trigger an Azure Function that runs a query and writes to blob storage or SQL.

7. Use Power BI Dataflows to pre-process and store filtered data, then export from there.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @ShubhaGampa11 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @ShubhaGampa11 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh