Slow excel performance

[Kfawcett12]

We currently are using a model on a P1 capacity that performs very quickly in Power BI desktop but a lot of our users want to use Excel which performs really slow when using multiple dimensions. We have run a profiler trace on our capacity and the MDX sent back form the pivot table is poorly written. Has anyone else run into this issue and what did you do to overcome it?

[lbendlin]

What you need to realize is that a Power BI dataset is a SSAS Tabular instance, not an OLAP cube.  Power BI uses DAX queries that run natively on the dataset.  When you use "Analyze in Excel"  you treat the dataset as if it were an OLAP cube, so you can run MDX queries against it.  This requires translation/emulation and will slow everything down.

Best to educate your users about using DAX queries from Excel instead of MDX.  We have seen a dramatic improvement in performance (and user satisfaction, frankly) with our most die-hard Excel users when we managed to persuade them to switch to DAX.

[Kfawcett12]

Thanks for the response! When you are suggesting users use DAX I am assuming you are having them use power query to get what they need?

We are really trying to push users to use power bi for their data exploration but there are always Excel hold outs.

[lbendlin]

No, Power Query uses M and is generally not accessible to report users.  DAX is the language to query datasets.