unable to use MDX query for direct query mode in Powerbi.

We are currently investigating if it's feasible to migrate to MS Fabric. To keep the cost under control, we need to get rid of the PPU license and use the Fabric capacity, which is very limited in memory, at least for us. 

The proposed solution is move to OneLake, which means not loading all data in Power BI memory but use Direct Query to query the data in the backend. Unfortunately our end users are using Excel to connect to our models, and the "Show Details" in a pivot is not supported with Direct Query. 

The error message is clear:

DirectQuery error: MDX/SQL operations are not supported in DirectQuery mode

And the documentation is also clear:

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh230898(v=sql.110)

Pivots created by Power BI (Desktop) instead of Excel don't have this problem.

As our end users are all Excel users (working in Finance) they are very hesitant to switch to Power BI, which also has limitations for their way of working. So this is blocking us to move to Direct Query / Fabric OneLake.

We need to use the DirectQuery with MDX/SQL operations in powerbi.