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 "put my 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]
    // via Model view > Advanced > Bind to parameter (Multi-select on)
    RegionList = if Value.Is(pRegion, type list) then pRegion else {pRegion},
    RegionSql = Text.Combine(
        List.Transform(RegionList, each "'" & Text.Replace(_, "'", "''") & "'"), ","),
    Filter = if 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 & "   -- 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 for other filters (dates, product, etc.) and they are all applied in the inner query. Escape values as shown so a selection cannot break the SQL.

2. Prefer views over custom SQL where you can. If the complex logic lives in a database view, Power BI generates simple SELECT ... WHERE 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.