Forum Discussion
DirectQuery workaround maximum allowed size with Top records
So what you are basically asking for is to change the 1 Mil error into a (silent) abort ? Feel free to raise an idea about that.
Currently it is not possible to influence the Direct Query queries generated by the user filter interactions. That might be another idea worth putting forward.
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?
Yes 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
Raise an idea at ideas.powerbi.com