Make DirectQuery and Query Folding more robust, supporting the TOP XXX command without crashing

[BarryASeymour]

I'm asking you to make DirectQuery and Query Folding more robust, supporting the TOP XXX command without crashing.

I have a report connecting to Amazon Redshift using DirectQuery. Query Folding is enabled. I was hoping to use the default method of filtering, where filters / slicers set by the user are automatically passed into the Semantic Model and query folding figures out how to modify the SQL to return the results I want.

It works, but if I clear all my default filters (setting it up for publish), the result set is too large and Power BI throws an error. If I filter rows in the query ("LIMIT 100" for example) then the report won't find the record I want if it's outside that initial result set. I tried setting TOPXXX rows in Power Query editor, but then query folding fails and throws an error.

I will  be getting help defining parameters, modifying my query, etc., but in the meantime could you guys make DirectQuery and Query Folding more robust, supporting the TOP XXX command without crashing?

[BarryASeymour]

My issue is not the TOP 100 functionality, it is the fragility of the Query Folding engine. I tried to apply a TOP 100 filter in power query editor and Power BI told me it could not fold the query. I wanted to submit a bug report, perhaps this is the wrong forum for that.

I will research your suggestion, thanks.

[v-xingshen-msft]

Hi @BarryASeymour ,

According to your description, your problem is centered on the inability to use the Topn function to collapse query data after clearing all the default filters, right?

Here are some suggestions we have for you to help you solve your problem.
1. We can try to customize the parameter and pass the reference back to our database to ensure the success of our query folding:
Let's take a Sql server database as an example to do this:

let
    Source = Sql.Database("VM", "2222"),
    
    Query = "SELECT TOP " & Number.ToText(RowLimit) & " * FROM dbo.[222]",
    LimitedSource = Value.NativeQuery(
        Source, 
        Query,
        [],
        [EnableFolding = true]
    )
in
    LimitedSource

After the execution is complete, you can see in the View Native Query option to see if it is successfully collapsed into a Sql statement, this logic also applies to Amazon Redshift

2.2. If your data source is very large, then you can also try to use a hybrid model to optimize the problem you are experiencing.
DirectQuery + Import Mode: Some tables use DirectQuery, some tables use Import Mode.
Aggregations: store aggregated data locally (Import), query detail data in real time (DirectQuery)
We can reduce the pressure on DirectQuery by importing a small portion of data locally for high-frequency queries.
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
We hope that the results of our tests will be helpful to you, and we would be honored if we could help you solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.