Help resolving "Error fetching data for this visual"

[ERing]

I have a simple dashboard with a few donut charts and a Matrix. The matrix continually fails to load and shows the error shown below "Error fetching data for this visual. The resultset of a query to external data source has exceeded the maximum allowed size of '2000000' rows."

I know the results for the matrix are less than 2,000,000 rows. In fact, most of the results are less than 100 rows.

I suspect the data model is causing the issue, but I'm unsure how to determine that to be the problem or how to resolve it.

The largest table in my dataset is around 15,000,000 records. I've attempted to change this table from Direct Query to Import, but Power BI simply won't process changing the table to Import.

I'm stuck and unable to solve this so any help would be greatly appreciated. 

[Anonymous]

Hi @ERing ,

DirectQuery defines a limit of 1 million rows for data returned from cloud data sources that are not local to any data source. Local sources are limited to a defined payload of approximately 4 MB per row (depending on the proprietary compression algorithm) or 16 MB (for the entire visual object).

Premium capacity allows you to set a different maximum row limit, and Power BI Premium capacity settings are provided in the portal, preloaded with default values. Administrators can view and override the defaults based on their preferences to better protect their capacity from issues and avoid problems before they arise.

More detailed information can be found at the link:
Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn
Power BI Premium new capacity settings allow for more control over datasets | Microsoft Power BI Blo...

Here is another solution to a problem similar to yours:

Solved: The resultset of a query to external data source h... - Microsoft Fabric Community

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Shivu-2000]

Hi @ERing 

Follow the given steps to find the problem and solve it as:

1. Use Performance Analyzer in Power BI Desktop to capture the DAX query for the matrix. Run it in DAX Studio to identify issues with query size or performance.

2. Optimize Data Model:

   • Filter Data: Apply filters in Power Query or at the source to reduce the dataset size.
   • Remove Unnecessary Columns: Keep only columns needed for the report.
   • Use Aggregation Tables: Summarize data and use these in visuals.

3. Adjust the Matrix Visual:

   • Simplify fields in the rows, columns, or values sections to avoid excessive granularity.
   • Ensure filters are applied to reduce the data being visualized.

4. Switch to Import Mode:

   • Break large tables into smaller chunks if Power BI fails to process them in Import Mode.
   • Optimize Direct Query tables with proper indexing or pre-aggregated views.

5. Power BI Settings:

   • Increase memory for queries: File > Options > Current File > Data Load > Maximum Memory Used per Query.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!