DirectQuery workaround maximum allowed size with Top records

[Anonymous]

Hi,

using SQL Server Direct Query

I have a standard fact table visualised as 'Table' and dimension tables as 'Slicers'

the fact table has 100 mil records, so depending on the filters, the amount of records returned can breach the Direct Query limits:

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

Is it possible to work around this limitation by simply telling the table to only load TOP N records, after the (optionally selected) filters?

Note that I cannot apply Top N in modelling, since that would first limit the records and then apply (selected slicers) filter. What I need is first apply the filters and then, the selected ordering (Table headers), and only then the record count limit

Any thoughts how to do this?

Kind regards,

Stefan

[lbendlin]

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.

[Anonymous]

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? 

[lbendlin]

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.

[Anonymous]

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

[Anonymous]

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)

[v-easonf-msft]

Hi, @Anonymous
Have you checked this similar thread to create a mesure ,the apply it to the visual filter pane.

https://community.powerbi.com/t5/Desktop/Error-Resultset-of-a-query-to-external-data-source-exceeded-the/td-p/739421 

best regards,
Community Support Team_ Eason

[Anonymous]

Hi @v-easonf-msft 

not sure how to combine RANKX with selected filters, is it possible?

Tried following this but I can't work out how to apply to my example