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 adds the slicer filters on the outside, so the database often has to evaluate the whole inner query first. The feature built for "inject slicer values inside my SQL" is dynamic M query parameters.

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

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

let
    // pRegion is an M parameter (type Text, "Required" off),
    // bound to Region[Region] in Model view > Advanced > Bind to parameter,
    // with "Multi-select" and "Include Select all" enabled
    RegionList =
        if Type.Is(Value.Type(pRegion), List.Type) then pRegion else {pRegion},
    RegionSql =
        Text.Combine(List.Transform(RegionList, each "'" & Text.Replace(_, "'", "''") & "'"), ","),
    Filter =
        if List.Contains(RegionList, "__SelectAll__") or List.IsEmpty(RegionList)
        then "1 = 1"
        else "region IN (" & RegionSql & ")",
    Sql = "
        WITH sales_f AS (
            SELECT order_id, region, amount, order_date
            FROM dbo.sales
            WHERE " & Filter & "            -- filter applied INSIDE, before joins
        )
        SELECT s.region, d.month, SUM(s.amount) AS amount
        FROM sales_f s
        JOIN dbo.dim_date d ON d.date_key = s.order_date
        GROUP BY s.region, d.month",
    Source = Sql.Database("server", "db", [Query = Sql])
in
    Source

Repeat with more parameters (dates, product, etc.) and they are all applied in the inner query. Escape values as above so a selection can't break the SQL.

2. Prefer views over custom SQL where you can: if the complex logic lives in a database view, Power BI generates clean SELECT ... WHERE queries against it that the optimizer can push down normally, which often fixes the issue without parameters.

3. Reduce how many queries fire: Options > Query reduction > add an Apply button to slicers/filters, limit visuals per page, and consider composite models with an Import aggregation table for the high-level 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 be applied inside the query
-> Apply button for slicers (query reduction)
-> Import aggregations for summary visuals, DirectQuery only for detail

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