Paginated report optimization

[Fonsi]

Is it possible to use a query parameter in a paginated report that sources its data from a Dataflow? Concretely: create a parameter inside the Dataflow (Power Query M) and configure the paginated report so that, when the report runs and the user supplies a project-number parameter, the report only retrieves rows for that project number from the Dataflow. I know this works when the paginated report runs a SQL query with a project-number parameter — can the same be done when the report’s dataset is built from a Dataflow?

[grazitti_sapna]

Hi @Fonsi 

As per my knowledge, It is currently not possible to directly create a query parameter inside a Power Query M Dataflow that dynamically filters data when used as a source for a paginated report in Power BI. Unlike SQL queries where you can define parameters that the paginated report passes for query folding and efficient filtering at source, Dataflows do not support parameterized queries in that way.

The reason being Dataflows are designed as reusable ETL data preparation layers producing static tables once refreshed. The Power Query M parameters inside Dataflows are primarily for design time configuration, not for runtime filtering via report parameters.

What you can do:

• Use report parameters in the paginated report to filter the data after it is retrieved from the Dataflow dataset. This means the full dataset from the Dataflow is fetched and filtering is then applied in the paginated report.
• For better performance and dynamic query filtering, it's recommended to use SQL or other queryable data sources that support query parameters at execution time.
• If you want dynamic filtering before data retrieval, consider creating a SQL view or table with parameter filtering and query that from the paginated report instead of the Dataflow.

Additional info:

• Paginated reports in Power BI Report Builder support creating report parameters that can be passed to dataset queries when the data source supports query folding/parameters (e.g, SQL Server).
• When using Power BI datasets or Dataflows as sources, report parameters typically filter the data on the report side rather than the data retrieval query.

Also sharing some links that might help:

• https://learn.microsoft.com/en-us/power-bi/paginated-reports/parameters/paginated-reports-create-par... 
• https://learn.microsoft.com/en-us/power-bi/paginated-reports/parameters/report-parameters-concepts-r... 
• https://learn.microsoft.com/en-us/power-bi/guidance/report-paginated-data-retrieval 

I hope this helps clarify the current capabilities and best practices around parameters with Dataflows and paginated reports.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[V-yubandi-msft]

Hi @Fonsi ,

@grazitti_sapna , has provided a helpful response to clarify your question. If you need any additional details or further assistance, please let us know.

Thank you and continue using Microsoft Fabric Community Forum.