Hi @PowerBI-ELM
Yes you're not the first to be caught by Direct Connection issues, and I doubt you'll be the last.
When you connect with a Direct Connection to a SQL database, Power BI generates an SQL query to populate each visualisation on the page. So if you have 40 visuals on page, that's 40 SQL queries. These are re-sent whenever a user clicks on a visual or slicer or moves to another page on the report. If that report is shared with others they will also generate 40 SQL queries each time they open the report, or select a visualisation or click a slicer etc. So with a report with many visualisation, and/or many concurrent viewers the SQL server gets many, many queries. If it is not configured to cope with this increased number of queries, as in your case, it may seriously degrade performance or in worse case crash the server.
Direct Connection mode is really only good when you have a Server that is configured to accept the load, and you absolutely must have real-time responses, or you have multiple millions (Billions?) of records that will not fit in a Dataset. You have to ask what action will be taken on the real-time data ? Will the users change the business activities immediately they see the data? Or will they take no action , or an action in, at least, and hour or so ?
If you change your connection to Import Mode, these SQL server problem go away, as the Database is only queried at refresh time, and the data is "cached" in the Dataset for report queries. You can configure automatic refresh to occur every half-hour during the business day (with a Pro licence, it can be more frequent with a Premium licence) and take the load away from your SQL server.
Hope this helps
Stuart
