Welcome to the forum – great to have you here!
I haven’t worked directly with a live connection (Direct Query Mode), but recently I helped another member who was limited to DQ mode develop what otherwise would be a straightforward solution in Import mode. I was absolutely floored by how limiting the DQ restrictions were. The thread below will give you a good sense of how these limitations played out in the development of a DQ-compatible solution. Also embedded in the thread is the link to a detailed SQLBI whitepaper that discusses all of the limitations of DQ in-depth.
I think all of your concerns stated are valid, and there are many other problematic aspects of DQ in my opinion that you didn’t mention. All of which makes me wonder if it might be possible in your case to do a full data export on a regular basis, and then import that data into Power BI , so that you have the full suite of PBI capabilities available to you?
I hope this is helpful.
- Brian