Multiple queries with multiple parameters

Hi Keshav1107​,

What you are describing is how DirectQuery treats a custom SQL query: Power BI wraps your SQL as a subquery and applies the slicer filters on the outside, so the database often evaluates the whole inner query first. The feature designed for putting slicer values inside the SQL is dynamic M query parameters.

1. Dynamic M query parameters (supported for many DirectQuery sources such as SQL Server, Azure SQL, Snowflake and Databricks; check the docs for yours):

1. Power Query: Manage Parameters > New > pRegion (Text), one per filter
2. Put the parameter inside the SQL (example below)
3. Create a small slicer table with the allowed values (no relationship)
4. Model view > slicer column > Advanced > Bind to parameter = pRegion
   -> enable Multi-select (and Select all if needed)
5. Use that column in the slicer

let
    // pRegion: M parameter (Text), bound to SlicerRegion[Region]
    // in Model view > Advanced > Bind to parameter (Multi-select on)
    RegionList = if Value.Is(pRegion, type list) then pRegion else {pRegion},
    RegionText = Text.Combine(List.Transform(RegionList, each "'" & _ & "'"), ","),
    Source = Sql.Database("server", "db", [Query =
        "SELECT region, month, SUM(amount) AS amount
         FROM (SELECT * FROM dbo.sales_by_month
               WHERE region IN (" & RegionText & ")) s
         GROUP BY region, month"])
in
    Source

Repeat for other filters (dates, product, etc.) so they are all applied in the inner query, and sanitize the values (for example only allow values that exist in the slicer table).

2. Prefer views over custom SQL where you can. If the complex logic lives in a database view, Power BI generates simple filtered queries against it that the optimizer can push down normally.

3. Fire fewer queries: Options > Query reduction > add an Apply button to slicers, limit visuals per page, and consider Import aggregation tables for summary visuals so only detail drill-downs hit the database.

So my overall approach would be:

Move complex SQL into views where possible
-> Dynamic M parameters for filters that must apply inside the query
-> Apply button on slicers (query reduction)
-> Import aggregations for summary visuals, DirectQuery for detail

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.