The following are some of the key causes of reports published in the DirectQuery mode Power BI Service that fail: configuration of data sources, gateway settings as well as query limitations. The following gives a detailed explanation based on the provided information by Microsoft:
1. Data Source Connectivity and Configuration
In Direct Query mode, the real-time connection is completely tied to the data sources. Thus, if the connection from the Power BI Desktop does not match the Power BI Service configurations, the following will happen: For instance:
In Direct Query mode, the data source must actually support it.
The credentials in Power BI Service have to be updated and authenticated with the datasets.
The data source timeout durations and unsupported SQL may also have caused the queries to fail.
2. Gateway Misconfigured
An on-premises data gateway is properly set up when there are on-premises data sources. Common things are as follows:
The gateway is not installed or is not running.
The dataset was not mapped correctly to the data source within the gateway.
The account used to configure the gateway needs proper permissions to access the data source.
Limitations of Query and Performance
Direct Query imposes limits in order to ensure performance and query execution.
Complexity of queries: If you have questions or DAX measures that are very complex, they may time out.
Data volume: Too much data can lead to excessive delays and can cause failures.
Unsupported features: Some features of Power BI, like calculated tables and certain transformations in Power Query, need to be fixed within DirectQuery mode.
Further, refer to Microsoft's document on Direct Query. The "Learn more about DirectQuery" documentation provides details on best practices for troubleshooting and optimizing DirectQuery implementations in Power BI.
