PowerPivot - Issues with running DAX/MDX to import data from cubes

Question

Hello,

We have had a new issue pop up with using DAX to import data to PowerPivot which we encountered last week and has never happened before.

• We firstly connect to our Analysis Services cube on PowerPivot in Excel

• And able to connect successfully to our on-premises cube

• On the following page, we then input our DAX Query and able to successfully validate it

• The issue that we have is when we then press 'Finish', then an error pops up which has never happened to us before.

• Now, when i click on the 'Import measures as text', it works but converts our measures into text. This is not ideal as almost all of our measures are numeric
• The measure I created for this example [TEST] is very basic

We currently have existing files with DAX queries in PowerPivot which do not have this 'Import new measures as text' ticked which can be refreshed but the problem is that we cannot change them anymore and save them on PowerPivot due to this error.

I have a few questions:

• Has anyone encountered this before as I cannot find anything related online?
• Is this potentially a new bug in PowerPivot/Excel, as we were able to retrieve our data this way before without errors. Our Excel version updates automatically and is currrently on the below version
  

Thanks

Answer 1

Hi @JT-gfk ,

May I know if you are using Multidimensional Model or Tabular Mode？

And what version are you using, SSAS 2012, SSAS 2014, SSAS 2016, SSAS 2017 or SSAS 2019?

I have tested the same approach with SQL Server 2016 tabular mode by using following DAX code and it works in my lab.

evaluate
SUMMARIZE('Date'
, 'Date'[Calendar Year]
, "Sales Amount (USD)", SUM('Internet Sales'[Sales Amount])
)

Firstly , please make sure it will work in SSMS, then we can copy the DAX query to EXCEL.

As you can see the results form SSMS and EXCEL, it works correctly.

Secondly, if I remember correctly, summarizecolumns was introduced after SSAS 2016, if you are using SSAS 2016 or previous version, then it may not work. That's why I would recommend you to make sure it can return results via SSMS.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.