399
When you use Analyze in Excel, MDX queries are sent to the Power BI dataset. If you have a PivotTable with 10 measures, the performance is usually slower compared to a similar Matrix in Power BI with the same content. Power BI generates DAX queries.
One of the reasons why this difference exists is that the DAX query is optimized using a process called "fusion", which includes in a single storage engine request the aggregations required for multiple measures, whereas this feature has not been implemented for MDX queries.
Implementing fusion for MDX queries would dramatically improve the performance of many pivot tables in Excel where there are many measures in the same pivot table.
STATUS DETAILS
Completed
Ideas Administrator

Shipped in October 2020.

Comments

M

This optimization has been implemented in Power BI Desktop October 2020. It is also available in Azure Analysis Services. You should close the item as "Implemented" !!

M

I agree.

Microsoft please implement a native DAX support as soon as possible!

M

We just encountered the same perf problems after deciding that we should try transitioning to tabular from multidim. Excel pivot tables are our main client to access our cubes, and the poor performances when querying tabular models might just completely stop us from taking this step forward. Native DAX support from Excel is really needed!

M

Power BI is good, but still a lot of users love excel pivot services.
So why not support DAX for pivot services to improve the query performance. The MDX is really performance killer for tabular cube if the dimension is large.

M

I noticed a new flag in the advanced options on my azure analysis services instance called "OLAP \ Query \ MdxFusionOptimizations" . Seems like Microsoft is working on something behind the scenes. Very curious to understand when this entire feature is going to see the light.

M

I agree. It is almost impossible to use Excel with a bigger tabular model. A query running in PowerBi less than 10 seconds takes almost 5 minutes to complete via Excel. Microsoft please implement a native DAX support as soon as possible!

M

Also take into account that every time you use Grand Totals in Excel, MDX queries are being generated with crossjoins which causes the performance of the queries to be quite poor specially when using attributes from 2 large dimensions, such as Customers and Products for example. It would help a lot having a DAX generator on the Excel side and depending on the Analysis Services (OLAP vs Tabular) use one or the other to keep compatibility.

M

Please improve, we are struggling at The Coca-Cola Company with the query engines, response, and error codes. Many tickets with the MS team. Many business folks use Excel and native BI is simpliy not the answer.

M

This is an imp update needed

M

I had to retrieve table from SSAS model using EVALUATE statement into local instance (Excel, Power BI Desktop). On 500K table, it took 1 minute in Power BI and 30 min in Excel to retrieve it. Native support of DAX in Excel is a must.