Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
Regards,
Charlie Liao
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.
Regards,
Charlie Liao
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |