Snowflake Dynamic M Query Parameter: "Query is too complicated to run" with 500+ items

[Arpit6075]

Hello,

I am encountering a critical issue with Dynamic M Query Parameters and Snowflake connectivity in Power BI Desktop.

Scenario:

• DataSource: Snowflake (DirectQuery).

• Feature: A Mass Filter Slicer is bound to a List-type Parameter (PartNumber).

• Implementation: The parameter is used within a Value.NativeQuery block to filter a large fact table.

  The Problem: When users select a small number of items, the query folds and performs perfectly. However, once the selection exceeds ~400-500 items, the visual fails with the error: "This query is too complicated to run" 

  Observations:

  1. I have confirmed that the SQL generated by the parameter creates a massive IN ('P1', 'P2'...) clause.

  2. I have tried using Text.Combine and Snowflake’s FLATTEN(SPLIT()) functions to pass the list as a single string, but I still hit complexity limits.

  3. Turning off EnableFolding is not an option as the base table contains millions of rows and causes a timeout.

     Question: Is there a known character limit for literal strings passed via Dynamic M Parameters in the Snowflake connector? Are there recommended patterns for "Semi-Joins" that maintain folding without bloating the Native Query SQL string?

     

     @#Snowflake, #DynamicMParameters, #QueryFolding, and #NativeQuery

     Any guidance from the community or the PG (Product Group) would be greatly appreciated.

[v-saisrao-msft]

Hi @Arpit6075,

Have you had a chance to review the solution we shared by @nathancwatkins? If the issue persists, feel free to reply so we can help further.

Thank you.

[Arpit6075]

Hi @v-saisrao-msft ,

Solution not helpled as replied answer given the Limitation not the suggested solution or answers

[nathancwatkins]

The answer is "What kind of system have you built that allows a user to select 400-500 items?" Maybe you need to put these items into categories, and pass those as a list. Also, if you are not already, wrap your list in List.Buffer--it makes a HUGE difference. Your list is already being added to the SQL as a single string, as evidenced by it appearing as the values in the IN[] clause.

Final thought, does your native query have to use that filter at all? In this case, it seems that you'd be better off not using that filter in the native SQL, and just use the slicer that you already built as an actual slicer for the data.

Are you running a query containing 4-500 items each time a user makes a slicer selection?

--Nate

[v-saisrao-msft]

Hi @Arpit6075,

When using Dynamic M Query Parameters with an inline multi-select slicer, Power BI inserts the selected values directly into the native SQL. If many values are chosen, this can result in very large literal expressions, causing the Mashup Engine to fail with the error This query is too complicated to run. This issue happens before the query executes and is due to Power BI’s internal complexity checks. To avoid this, it’s recommended not to use large list-based parameters. Instead, apply set-based filtering, such as semi-joins with a table containing the selected values. This approach keeps the SQL compact, maintains query folding, and scales well with Direct Query.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Thank you.

[nathancwatkins]

Yeah, the SQL query has a limit of 32,000 or so characters.

--Nate