Forum Discussion

Sanjota05's avatar
Sanjota05
Frequent Visitor
5 months ago
Solved

Dynamic M query parameter binding to a query table which uses import mode

At the moment I am using a dynamic M queries within a SQL queries which is in import mode. I have 9 custom sql queries where the data is being filtered in these queries by adding parameters in where ...
Need Help
Show and Tell
Tips and Tricks
  • Natarajan_M's avatar
    Natarajan_M
    5 months ago

    Hi Sanjota07 ,


    Non-null assertion failure: resource: Resource should be available for query Query2 to store FoldedArtifacts in cache This corresponds to that the pbi engine cant fold the entire query in direct query mode so this flag is raised 

    You can also try this approach :
    let
    Source = Sql.Database("",""),
    PrCase = Source{[Schema = "dbo", Item = "Table"]}[Data],
    FilteredRows = Table.SelectRows(PrCase, each [Active] = #"Active")
    in
    FilteredRows

    Thanks

v-hashadapu's avatar
v-hashadapu
Community Support
5 months ago

Hi Sanjota05 , Thank you for reaching out to the Microsoft Community Forum.

 

In Power BI user driven filtering that happens before data is loaded only works with DirectQuery using dynamic M parameters. In Import mode, parameters are evaluated only at refresh time, so slicers cannot change them dynamically. That’s why you don’t see the Bind to parameter option, it simply isn’t supported in Import.

 

The reason you’re stuck in Import mode is your query design. Even though you used Value.NativeQuery, embedding parameters inside a custom SQL string with patterns like IN and CAST prevents Power BI from validating and folding the query into a native DirectQuery statement. When a query isn’t foldable or safe for DirectQuery, Power BI either forces Import or blocks conversion, which is exactly what you’re seeing with the errors and unknown source in TMDL.

 

If you need dynamic, slicer driven filtering before data is loaded, you must restructure the query to be foldable, avoid custom SQL strings and apply filters in M so Power BI can push them down and then use DirectQuery. If that redesign isn’t feasible, then you’ll have to stay in Import mode and handle this through pre-filtering and refresh, because dynamic parameters at runtime are not possible there.

 

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

DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft Learn

Sanjota05's avatar
Sanjota05
Frequent Visitor
5 months ago

Hello v-hashadapu ,

 

I have removed the custom sql , then loaded data and applied changes to modelling. The table is still in import mode even though I had removed the custom strings like IN and CAST. When I tried to change storage mode using TMDL, I got unknown server and database.

 

The redesigned query:

 

Sqltext="Select * from orders where orderID =@porderID and caseID >@pcaseIDstart and caseID<@pcaseIDEnd and solutionstatus=1 "

 

I need to stay in import mode and I need to pre filter data and refresh. Dynamic parameters are not possible in this case. 

 

There are no other ways in this case?