Multiple queries with multiple parameters

Dear experts,

a { text-decoration: none; color: #464feb; } tr th, tr td { border: 1px solid #e6e6e6; } tr th { background-color: #f5f5f5; }

1. Problem Statement

Power BI dashboards are experiencing poor performance and high load times because they use Direct Query with complex SQL queries. Every user interaction (filters/slicers) triggers database calls, resulting in excessive data processing and increased database load because Slicer values/ multiple parameters are not able to passed to queries, ideally slicer values should be injected as conditions in multiple queries. currently these slicer selected values are injecting towards end of the report query, means first queries bringing entire data then filtering, this is causing massive burden on db end.

2. Current Behavior

• Dashboards operate in Direct Query mode.
• Each slicer/filter selection sends a query to the source database.
• Large datasets are retrieved and processed before filters are effectively applied.
• Multiple user requests generate frequent database hits, causing slow report response times and performance issues.
• Reports provide real-time data but with degraded user experience due to latency.

3. Expected Behavior

• Optimize the reporting solution to significantly improve dashboard performance and reduce database load.
• Ensure filters are applied as early as possible in query execution to minimize data processing.
• Leverage approaches such as Semantic Model optimization, Incremental Refresh, Stored Procedures (SPs), and Table-Valued Functions (TVFs) to improve efficiency.
• Maintain real-time (or near real-time) reporting capabilities while delivering an acceptable user experience aligned with business requirements.