Michel, just a note....

You can have a view in SQL Server that queries tables. And obviously the view can have CTEs...

In your PBI report, you can define direct query (as opposed to in-memory load), and reference the view.

I will try it tonight to be sure, but I'll be kind of stunned if that doesn't work. I don't use direct query very much and I know there are a few limitations, but that particular one would be beyond crippling.