In order to optimize DirectQuery in the Power BI Service, it is pertinent to consider optimizations for functions, the design of a data model, and knowledge of how a gateway operates. DirectQuery Lives sends queries to the data source with the slightest interaction. So, to get the best performance, keep query counts and complexity as low as possible. For ease of performance, a few helpful features include using query reduction settings, such as disabling auto page refresh and limiting slicer interactions.
When running, set up the data model for performance-characterized design—no complex calculated columns or DAX measures; star schema types with well-indexed source tables; keep visuals per page to a minimum; pre-aggregate data wherever possible; and only check "Include in report refresh" for necessary visuals to all reduce query load while using reports.
For connectivity, the on-premises data gateway must be updated with enough capacity to execute the data queries and installed in proximity to the data source to minimize latency. For heavy-use visuals, set up scheduled cache refreshes and create query folding to push filters to the source. Lastly, keeping track of and optimizing performance in DirectQuery may be achieved by considering tools such as the Power BI Premium Capacity Metrics app and Azure Monitor.
