PowerBI DirectQuery Performance with Measures

Hello,

We had the exact same issue, sometimes the report took 60sec or more to load.

The best trick to reduce time was to split the report in to two or three tabs reducing the number of concurrent queries to the server.

Another bug(?) we have found is the Top N filter (in one report I had it for 6 different metrics). Removing that and displaying the information in an other way increased the performance significantly.

And ofcourse there is always the solution of upgrading your SQL.

As this is still an issue I would love to hear anyone else's ideas also.

ty,

C.K.

p.s. 

having the measures calculated in the database and not in the model would also help but that was not an option for us. allowing unrestricted measures in the Direct Query hurts also the performance. (check File > Options > Direct Query). Not allowing them though, would significantly limit the funtions you can have in the model.  

mmitch2k

I've also experienced the performance issue in DQ mode. What I've found is the slow execution plan in database side. As the sent underlying query has some where clause like "CAST(yourIndexColumn as int) = 1", the CAST would not allow the index to be used, so creating index won't work in many cases. 

Also the Direct Query limitation has stated measure performance shortage. So, to have a acceptable performance, either use less measures or high tier Azure SQL database.

Hi mmitch2k,

Yup, There are some limitation of DQ and PBI team are trying enhance it. Hope you share your opinion in this survey to help them clarify our need: https://powerbi.microsoft.com/en-us/blog/help-improve-directquery/