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

Hi Anonymous ,

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.