Multiple queries with multiple parameters

Here's a detailed and professional response you can post:

Hi All,

Thank you for the suggestions provided so far.

Let me explain my scenario in more detail.

I have a Power BI report with multiple slicers (for example: Region, Country, Business Unit, Date Range, Product Category, etc.). These slicers are currently built using a static/disconnected table.

Behind the report, I have 4-5 custom SQL queries (DirectQuery) that retrieve data from different database tables/views. My requirement is to pass the user-selected values from these slicers into all of these custom queries so that the filtering happens at the database level before the data is returned to Power BI.

The main reason for this requirement is performance optimization:

• Some of the custom queries are quite complex and involve multiple joins and aggregations.
• A few queries already take a considerable amount of time to execute at the database level.
• Currently, the queries fetch a larger dataset and the filtering is applied later within Power BI.
• When multiple users access the report simultaneously, multiple complex queries run against the database, resulting in increased database load and slower report performance.

Ideally, I am looking for something similar to:

1. User selects values in slicers.
2. Selected values are passed as parameters to all relevant custom queries.
3. Database returns only the filtered subset of data.
4. Power BI renders the report using that reduced dataset.

For example:

• Region = APAC
• Country = India
• Business Unit = Sales

These selected values should be available inside multiple custom SQL queries and be used within the WHERE clause during query execution.

I have explored dynamic M query parameters, but my understanding is that there are limitations when multiple slicers need to drive multiple custom queries simultaneously.

My primary goal is to push the filtering logic to the source database and avoid retrieving unnecessary data into Power BI.

Any guidance, implementation examples, or recommended approaches would be greatly appreciated.

Thanks in advance!

Hi Keshav1107​ 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

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.

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.

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.

i think the exact feature you are looking for to fix this is called dynamic m query parameters. this is literally built for your exact problem.

instead of power bi pulling the whole table and then filtering it locally, dynamic m parameters let you bind a report slicer directly to a power query parameter. that parameter gets injected directly into your native sql query (or tvf/stored procedure) so the database only processes and returns the exact sliced data.

here is how you set it up:

first, go into power query and create a new parameter.
next, modify your directquery sql statement to include that parameter in the where clause.
close and apply. then go to the model view in power bi desktop.
click the column you are using for your slicer. in the properties pane on the right, expand the advanced section, look for "bind to parameter", and select the m parameter you just created.

now, whenever a user clicks that slicer, power bi passes the value directly into the sql query before hitting the db. it makes a massive performance difference and keeps your real-time requirement intact.

here is the ms docs walkthrough to get the exact syntax right, especially if you need to allow users to select multiple values at once in the slicer: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

hope this solves your directquery nightmare! if this gets your dashboard running fast again, please mark this as the accepted solution so others can find it.