When publishing to the Power BI Service, failures with DirectQuery mostly consist of gateway configuration issues, authentication problems, query timeouts, or unsupported settings from the data source. Here is an overview of the usual culprits and potential solutions:
1. Gateway Configuration Issues
Cause: For on-prem data sources, if the respective Power BI Gateway is missing or improperly configured, the Service cannot connect to the database.
Solution:
Ensure that the on-premises gateway is installed and configured correctly. Confirm the presence of correct data source mapping in Manage Gateways. Verify that the gateway is online and is approved for DirectQuery connection tasks.
2. Authentication & Permissions Problems
Cause: Power BI Service uses cloud authentication; however, it may not match the credentials being employed in Power BI Desktop.
Solution:
Once you are in Power BI Service, navigate to Settings > Data Sources and reenter credentials (valid OAuth, Windows, or SQL authentication). Make sure that the service account has read access to the data source. In the case of Azure SQL or any other cloud source, check if Firewall Rules allow Power BI Service access.
3. Query Performance & Timeouts
Cause: DirectQuery is a technology based on live queries, meaning that if the database is slow or the query runs too complex tasks, it may face timeouts.
Solution:
Simplify DAX measures and Power Query transformations so that they offer less complexity to queries. Improve performance via database indexing and possibly query tuning. If available, extend the timeout settings in the Power BI gateway.
4. Unsupported Features in DirectQuery
Cause: Certain functions in DAX, calculated columns, and relationships apply differently or remain unsupported in DirectQuery.
Solution:
Avoid slightly heavy computed columns; use measures instead. Make relationships one-sided whenever possible and eliminate cross-filtering. Try to optimize performance-hungry calculations using Hybrid Mode (DirectQuery + Import).
