The result set of a query to external data source has exceeded power bi error power bi direct query

[Anonymous]

I am trying to calculate rank for a dataset connected via direct query. It is giving below error when adding rank measure to the table visuals, else data is loading. 

"the result set of a query to external data source has exceeded power bi error power bi direct query"

Rank = RANKX(ALLSELECTED(Query2[Value]),CALCULATE(SUM(Query2[sales])))

can you please help whta needs to be change to the measure to make it work.

[Anonymous]

Hi @Anonymous,

AFAIK, there's a limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source when you work on direct query mode.
For this scenario, you may need to optimate the query (e.g. reduce category fields amount)to confirm these aggregated records are less than 1M rows.

Regards,

Xiaoxin Sheng