Visual With Direct Query Works in One Page But Not Another Page

[Anonymous]

Greetings,

I'm using direct query as a data source and I have a couple of pages created. The first page I have a gauge with a visual that takes the median of a column and displays the results (this is not a measure, just hardcore power BI from the fields pane). Works perfect, no issues. On my other page, I create another visual to try to use the median again using the same column and get this error:

The result set of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

I have tried to copy the visual but the same thing again, error. Does anyone know why this is working in one page and not the other? Secondly why is Power Bi returning all these rows when it only needs to return a single value?

[Anonymous · Accepted solution]

@Anonymous @amitchandak  Thanks for your responses, I think I've been able to work around it, I did not notice there was a filter on my first page where it worked, although the filter did not filter that much out. However, with this applied my belief is that it's created a WHERE clause in the SQL PBI used to get the data that did not pull everything. I have not had time to troubleshoot it at the backend, but my belief is the raw sql was pulling more data than was required hence exception, by applying the filter to the page or visual the problem went away.

[Anonymous]

Hi @Anonymous ,

So you just connect to a datasource in DirectQuery mode and then choose one column to create a gauge visual, it works right?

Have you tried to create a new visual use the same column in the same page? Is that worked?

Best Regard,

Yuna

[amitchandak]

@Anonymous , How you have created that measure. If you are using measure, which needs to bring in row-level data like sumx etc and it ends up bringing 1M rows then it will not work

refer this :https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding