Forum Discussion

BIanon's avatar
BIanon
Icon for Helper V rankHelper V
3 years ago

Anlyze in Excel performance issues

Hello Community,

I have re-designed one of our Power BI Datasets and after the redesign measures and visualisations in Power BI are performing noticably better. I have run benchmark tests using DAX Studios and confirmed that my measures have gotten faster.

Now, I'm trying to switch the data source from an old Pivot model connected to the PBI dataset but I keep running into memory cap issues. I have also tried rebuilding the entire Pivot table but also face memory cap issues quickly - much quicker than the old model.

I realize Excel queries the model using MDX, but I am unsure how to troubleshoot my measures that are performing much better in Power BI but are seemingly much, much worse when run in Excel.

Is there a way to extract the underlying query that gives more detail than what Microsoft suggests here:
https://learn.microsoft.com/en-us/office/vba/api/excel.pivottable.mdx

Also are there best practises for performance tuning a PBI dataset for Excel Pivot tables?
Can I build the same two Pivot tables connected to a new and old version and compare the queries constructed by both tables to narrow down the issue? How would you go about troubleshooting this?

Need Help

2 Replies

    • BIanon's avatar
      BIanon
      Icon for Helper V rankHelper V
      3 years ago

      Thank you for your response and for bringing my attention to this feature.

      Being able to execute DAX against my model is much preferred to MDX.

      Is there a way to use this feature but to still allow report users to choose their own measures/data points?

      Just on the surface level it feels like I have to choose between good query speed delievered by the connected experience versus the flexibility of Analyze in Excel where report users can cross data any way they like.

      Can this new connected experience be configured to deliver the same level of flexibility?