cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisWilliams
Helper II
Helper II

Top N query with direct query

Hi all,

 

I'm using Azure SQL and direct query.  I have a single table that has about 1.7 million rows in it and I want to offer a simple "top 10" style grid that includes a name and the value, like this:

 

Top 10 users:

user1      102

user2       98

 

etc.

 

I'm using the built-in Top N filter, which I create and apply to the name column.  I had anticipated that the query that goes back to Azure SQL would include a TOP N query and an OrderBy command, but that is not apparently the case.

 

I'm getting an error in Power BI desktop that my visualization has exceeded one million rows: 

"The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

 

So, I'm assuming that PowerBI is downloading all of the 1.7 million rows to the client and then doing the Top N filtering locally.  Which is not ideal.

 

I know I can create a view on the server that includes the top N users, but is my understanding of that the built-in Top N filter accurate?  Does anyone have any other ideas to do Top N filters against large datasets using direct query?

1 ACCEPTED SOLUTION
v-caliao-msft
Microsoft
Microsoft

@ChrisWilliams,

 

You could write query when connect to Azure SQL database where you can use top function. Or you click Edit Queries, order by one of you column, then use Keep Rows to filter top N rows.
Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

3 REPLIES 3
v-caliao-msft
Microsoft
Microsoft

@ChrisWilliams,

 

You could write query when connect to Azure SQL database where you can use top function. Or you click Edit Queries, order by one of you column, then use Keep Rows to filter top N rows.
Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

Hi, 

 

Does this always return 10 rows even if a filter is applied?
For eg, If I select a county as a filter,I want the table to show top 10 rows for that country.

 

Thanks

Hello everyone, I am try without success to replicate the same solution as above using AWS RedShift.  If I add the "Keep Top Rows" step in Power Query, I receive the following error in Power BI Desktop when I try to use any visualization with any field of my table.
 
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..
 
Without the "Keep Top Rows" step, I can successfully use a "Stacked Column Chart" visualization to show the number of entries per date in the table and the SQL query sent to the DB actually contains the limit check "select top 1000001 ...".  I would have thought that the passed SQL query would have simply contained a topn embedded in a topn like the following which runs succesfully in RedShift.
 
SELECT TOP 1000001 *
FROM
 (SELECT TOP 1000 "date", COUNT("date") AS "C1"
 FROM "my_table"
 GROUP BY "date")
 
Is this a bug or a limitation in the RedShift data connector for Power Query?
Thanks

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.