Forum Discussion
Excel pivot on analysis server slow, same table selection in powerbi desktop is fast
Johan wrote:What is the difference? Both are live connections on tabular, no import?
The difference is that Excel sends MDX queries and Power BI sends DAX queries.
DAX is the "native" language of a tabular model. While MDX queries potentially have slightly different semantics. So while a lot of queries will have similar performance there are some edge cases where the engine has to do a lot more work in order to build the sort of result set that Excel expects. It sounds like you have hit one of those cases
Yes, I found out that when you put a dax (evaluate) formula in the text field of the connection properties, it is much quicker and even imports the data into Excel.
Also I found out that the problem occurs when you place 2 dimension fields next to eachother, without a measure. In PBI (DAX) no problem, it is in Excel (MDX).
Conclusion is to find workarounds and best practices. It is undocumented behaviour.
Thanks all.
Johan wrote:Also I found out that the problem occurs when you place 2 dimension fields next to eachother, without a measure. In PBI (DAX) no problem, it is in Excel (MDX).
With Excel Pivot tables it's always been a best practice to start by adding a measure to the pivot table, then start adding dimension fields. Otherwise the pivottable generates a cartesian product of all the possible member combinations.
Power BI has a similar behaviour, but the UI actually generates an implied measure for you to prevent this (which you can do much more efficiently in DAX)
Thanks. The 2 fields come from a related fact table and dimension table.
Like: dimCustomer.City and factSales.ProductGroup.
But apparantly indeed Excel Pivot handles this differently then PBI-desktop. I thought it was the tabular model engine managing this, but apparantly it's the client's behaviour.