Trivial queries not folding

[nicbi]

I'm looking into using PowerBI to expose some data we have in Snowflake. The tables are huge so query-folding is a must, but I can't make it work even on the most basic queries.

Using Power BI Desktop Version: 2.103.881.0 64-bit. Connecting to Snowflake with either the "Snowflake" data source, or via the ODBC option. Using Direct Query mode, and not using native queries.

As an example I have the simplest table possible: `CREATE OR REPLACE TABLE numbers AS SELECT $1 AS id, $2 AS value FROM VALUES (1,1),(2, 3), (3, 25), (4, 15), (5, 4), (6, 12);`.

I want a visualization that shows the top 3 numbers by value, so I add a column chart, put `ID` as the axis and `VALUE` as the values. I add a filter to ID: TopN, N=3, By value: `VALUE`.

I would expect a query like `SELECT id, value FROM numbers ORDER BY value DESC LIMIT 3`. Instead, 2 queries are sent: the first selects all the data from the table, and the second queries the top 3 rows by their ID.

Am I missing something? How can I get the expected query? All of the documentation I can find implies something like this should fold.

[nicbi]

What a crazy feature gap!

[v-jingzhang · Accepted solution]

Hi @nicbi 

You can refer to this document: https://docs.microsoft.com/power-bi/connect-data/desktop-directquery-about#:~:text=TopN%20filters%3A.... It has explained this behavior as below.

TopN filters: Advanced filters can be defined to filter on only the top or bottom N values ranked by some measure. For example, filters can include the top 10 categories in the previous visual. This approach again results in two queries being sent to the underlying source. However, the first query will return all categories from the underlying source, and then the TopN are determined based on the returned results.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.