direct query datapoint limit in service

[lgroger]

In the power bi service, I am getting the error for a table usinig Direct Query: "The query result is too large. Consider removing unused columns, adding visual filters, or reduce the number of string type columns. " I only have about 20,000 rows so can someone clarify what the limit is so I can try to tune my table accordingly?

[v-diye-msft · Accepted solution]

Hi @lgroger 

You can optimize the performance by below 

1. The relational database source can be optimized in several ways, as described in the following bulleted list.

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-data-source-p... 

2. A DirectQuery model can be optimized in many ways, as described in the following bulleted list

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-model-design 

3. Optimize the report design:

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-report-design... 

4. The benefits of Import and DirectQuery models can be combined into a single model by configuring the storage mode of the model tables. The table storage mode can be Import or DirectQuery, or both, known as Dual. When a model contains tables with different storage modes, it is known as a Composite model. For more information, see Use composite models in Power BI Desktop.

There are many functional and performance enhancements that can be achieved by converting a DirectQuery model to a Composite model. A Composite model can integrate more than one DirectQuery source, and it can also include aggregations. Aggregation tables can be added to DirectQuery tables to import a summarized representation of the table. They can achieve dramatic performance enhancements when visuals query higher-level aggregates. For more information, see Aggregations in Power BI Desktop.

Alternative, you can use the performance analyzer to exam report element performance https://docs.microsoft.com/en-us/power-bi/desktop-performance-analyzer Or the dax studio: https://community.powerbi.com/t5/Desktop/VAR-and-FILTERS-combination-to-make-an-efficient-column/m-p... 

[venal]

@lgroger 

Please refer the link.

If you have any concerns, please let us know.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @

BR

Venal.

[lgroger]

I saw that post but I couldn't find anything in that linked article referring to the maximum data points a table will display is 3,500. I'm able to show some tables with 20,000 rows successfully so the max has to be over 3,500 cells.