MDX Compatibility = 2 OLAP SSAS Excel Power BI Desktop

Hi by-myselff ,

 

Yes, MDX Compatibility is set as 1 by default.

I found a way to configure it, refer to this document: https://docs.microsoft.com/en-us/analysis-services/instances/connection-string-properties-analysis-services?view=asallproducts-allversions#bkmk_reserved

The default ADOMD.NET connection string in Excel specifies the data provider, server, database name, Windows integrated security. The MDX Compatibility level is always set to 1. Although you can change the value for the current session, Excel will reset MDX Compatibility to1 when the file is next opened.

Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008R2;Data Source=AW-SRV01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

 

by-myselff ,

See if these can help

https://support.office.com/en-us/article/analysis-services-mdx-query-designer-power-pivot-30ab91f9-82a6-4f95-a5ec-2b6b7ab5cbcf

https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-query-fundamentals-analysis-services?view=asallproducts-allversions

https://blog.crossjoin.co.uk/2015/07/29/running-your-own-mdx-and-dax-queries-in-power-bi-desktop/

https://community.powerbi.com/t5/Desktop/limit-SSAS-data-import-through-MDX-DAX-query/td-p/700566

https://community.powerbi.com/t5/Desktop/How-to-change-MDX-query-from-Desktop-to-SSAS-live-connection/td-p/169163