PowerBI reports run slowly in DirectQuery mode

Question

I have a powerbi report for finance. Users need to see the latest data in real-time, so I have to choose DirectQuery. But in this mode, some functions, such as DateAdd and DatesMtd, cannot be used (This DAX function is not supported for use in DirectQuery mode.),
So I need to write a very complex SQL statement to achieve the equivalent effect, but this makes the report very slow (more than 10 seconds) every time it runs, and the largest table in my data model is less than 80000 rows. I've tried to optimize the SQL statements, but it doesn't help. Any solution?

(I use powerbi report server with sqlserver enterprise version)

Answer 1

Of course, with no information, I can't know what's taking so much time, but in order to understand what's happening you can use the following tools:

1. PowerBI Performance Analyzer: This will tell you what part is taking the most time. for more info see MSDocs & SQLBI
2. Check the datamodel and the storage mode of each table involved (ie: fact table, calendar, customer, etc). When querying the source, PBI won't use filters (directly in the query) that come from tables in import mode. (search for "composite models" on the web)
3. Limit the number of objects, for each object in the dashboard a query will be sent to the datasource, limiting the number of objects might help. (remember that objects wait for each other, so one slow loading object might cause your problem)
4. (even if you probably already did it) Have a look at the query execution plan, you can also check it for queries automatically created by PowerBI by capturing them (the easiest way is to use SQL Server Profiler)

I think that just by using PowerBI Performance Analyzer you will be able to see where the problem is, and then do more accurate search about it.