Couldn't load the data for this visual and Resources Exceeded error

[changhai]

I ran the report via PowerBI Desktop on Local PC is normal but after publishing to my workspace it show different errors for each mode.

For Import Mode:

the UI error is  like below:

Couldn't load the data for this visual

Please try again later or contact support. If you contact support, please provide these details.

• Activity ID4ef42b57-4293-42af-9acb-b21f65bbfe64
• Request IDbe2cd52e-2d79-857b-2c44-af32a54bc639
• Correlation IDfd036975-9091-c5da-9ab5-cfb1ee1feb9a
• TimeMon Jan 06 2020 11:27:39 GMT+0800 (China Standard Time)
• Service version13.0.11747.315
• Client version1912.2.031
• Cluster URIhttps://wabi-us-east2-redirect.analysis.windows.net

I used the developer tool of the browser and found the response is like below:

{"jobIds":["44e93a4d-7679-4e10-bed6-e92157d4812e"]

"odata.error":{"code":"DataExtensionError","source":"PowerBI","message":{"lang":"en-US","value":"Failed to execute the DAX query."},"azure:values":[{"timestamp":"2020-01-06T03:26:32.734915Z"}]  but actually I deleted everything related to DAX.

For Direct Query mode:

the UI error is like below:

Resources Exceeded

This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed. Please try again later or contact support. If you contact support, please provide these details.

• More detailsThe XML for Analysis request timed out before it was completed. Timeout value: 225 sec.
• Activity ID4ef42b57-4293-42af-9acb-b21f65bbfe64
• Correlation ID0ba1581c-3efa-2f48-627a-d19b57c7dcaa
• Request ID2aa19940-66a4-22e4-8719-767ba9da0d89
• TimeMon Jan 06 2020 11:33:44 GMT+0800 (China Standard Time)
• Service version13.0.11747.315
• Client version1912.2.031
• Cluster URIhttps://wabi-us-east2-redirect.analysis.windows.net/

The response error is like below:

{"jobId":"e84af602-f0e4-4c55-ae3c-6c3308ad5174",dsr":{"DataShapes":[{"Id":"DS0","odata.error":{"code":"rsQueryTimeoutExceeded","source":"User","message":{"lang":"en-US","value":"Failed to execute the DAX query."},"azure:values":[{"timestamp":"2020-01-06T03:33:34.655293Z"},{"details":"The XML for Analysis request timed out before it was completed. Timeout value: 225 sec."}]}}]}}}}]}

Seems it is timeout issue, where can I adjust the timeout value?

How do i debug these issues, on powerbi desktop they all worked well, is this a bug in powerbi service?

Thanks!

[venal]

@changhai 

Can you please share the below details.

1. Data Source

2. a. Connectivity Mode - Import -> Is the issue is coming only the particular visual level or report level.

    b. Connectivity Mode - Direct Query -> Is the issue is coming only the particular visual level or report level.

If you are not aware, please try to access the report and share us the screenshot of the error message.

3. Gateway version, If it is the older version please try to upgrade to the latest version of the Gateway.

If you have any concerns, please let us know.

BR

venal.

[changhai]

The datasource contains two parts:

a table from postgres (about 10w rows data) using import mode

another table from Azure Datawarehouse(about 200 million rows data) we try both import mode and directy query mode.

Import mode error, it is report level and always happen, I capture the reponse of the request, seems the detail error message is "Failed to execute the DAX query", but actually I deleted everything related to DAX.

Direct Query mode error, sometime it is report level and sometime it is visual level，the most surprising thing for me is that the SQL sent to the backend is exactly the same, why some success and some failure. Seems the default timeout is 225s.

I wanna how can I set timeout value to a  longer value.

Currently we don't use gateway.

Thanks

[venal]

@changhai 

Thanks for the quick reply.

Can you please drag slicers and try to reduce the data in the visuals and try to re-pro the issue.

If you have any concerns, please let me know.

BR

venal.

[v-xicai]

Hi @changhai ,

Currently, there is only issue about singing in Power BI Service, see in Power BI Support .

For your issue, there are several points you may considerate:

1.If your dataset contains on premise data source, whenever using Import mode or Directquery mode, you may need to install and configure gateway, see more: On-premises data gateway , Manage data sources, Merge or append on-premises and cloud data sources. And you can also configure schedule refresh for the dataset, see more: Configure scheduled refresh .

2.Power BI Pro or Power BI Free supports uploading Power BI Desktop (.PBIX) files that are up to 1 GB in size. Power BI Premium supports uploading Power BI Desktop (.PBIX) files that are up to 12 GB in size. If you need to upload PBIX file that is larger than 12 GB, consider to move data from your source to SQL Server, Oracle or SSAS, then use DirectQuery/Live connection.

3.For the error message "Resources Exceeded", there is a one-million-row limit for returning data when using DirectQuery. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error. See more: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#limitations-of-directquery.

You may need to filter the visual to reduce the amount of data in the result currently, and reduce complex calculation in data model. You can also try to improve the performance:

Power BI Performance Tips and Techniques

Data Import Best Practices in Power BI

4.If the Oracle database is very large, you may use Incremental refresh for your data source, the Incremental refreshes only data that has changed. Since incremental refresh is Premium only feature, so you may need to purchase Power BI Premium capacity for your organization, see How to purchase Power BI Premium. Incremental refresh policies are defined in Power BI Desktop and applied when published to the Power BI service. See more detail steps: Incremental refresh in Power BI Premium .

You may resolve the issue via the troubleshooting link :https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios .

Best Regards,

Amy

Community Support Team _ Amy

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

[changhai]

Hi @v-xicai  Thanks for your reply. I don't know how the gateway can help with these issues of my published reports, according to my understanding, this is only affects refreshing data, not query performance.

In fact, the size of my PBIX file is not reach the limition.

My question is why the powerbi service return the below error even I didn't use anything related to DAX.

{"jobIds":["44e93a4d-7679-4e10-bed6-e92157d4812e"]

"odata.error":{"code":"DataExtensionError","source":"PowerBI","message":{"lang":"en-US","value":"Failed to execute the DAX query."},"azure:values":[{"timestamp":"2020-01-06T03:26:32.734915Z"}]  but actually I deleted everything related to DAX.

The second question is my query result will also not hit the one-million-row limit with direct query mode, I just found the error

{"details":"The XML for Analysis request timed out before it was completed. Timeout value: 225 sec."} so my question is

for direct query mode, how can I set timeout value to a  longer value.

Thanks