Forum Discussion
DirectQuery workaround maximum allowed size with Top records
Not (silent) abort, just limit the amount of records loaded to a small number that the user is actually looking at.
There is little sense in loading 1 mil records into a Table in one go, the user can only ever see a handful at a time. So table could use (standard) virtual scrolling/paging to avoid unnecessary big queries.
The problem is even worse after publishing to web, which is further limiting the amount of data in one request.
Is that not an existing feature that can be turned on, or achieved with DAX?
Super UserYes and no. If you look at the performance analyzer for the DAX query you will see that a TOP x limiter is included but it is pretty big, in the 500k range I think.
What you can do is add a Top N visual filter that should technically override the automatic one.
Yes it's the max limit:
// SQL Query
SELECT
TOP (1000001)...
Adding a Top N "Filter on this visual" (table) seems to be doing something more complicated - joins to another SQL statement which is selecting Top N records by specific column, rather then just overrinding Top (1000001) and hence obeying the selected table sort order
- lbendlin
Raise an idea at ideas.powerbi.com
I can't even do that, thanks to (excellent!) the PowerBi account restrictions/changes. Took me ages to be able to post on this forum, this is ridiculous to be honest (nothing to do with this issue apologies)