Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Excel DAX query will not refresh

Some weeks ago I created a DAX query through a BI-generated connection so that I can analyze raw BI data in Excel.  I took the following steps:

 

1.  Publish the dataset from BI Desktop to BI.com.

2.  From the ellipses in the upper-right corner of my BI.com workspace, select "Analyze in Excel".

3.  Open the auto-generated Excel sheet.  The sheet contained a single connection, of type "OLE DB Query", and a connection string.  The default Command Text was "Model".

4.  Replaced the command text with

 

evaluate ( summarize ( 'Query', 'Query'[Desired Column 1], 'Query'[Desired Column 2] ... ) ) order by 'Query'[Desired Column 1]

 

5.  Closed the connection.  A table was generated in Excel with my desired columns.

 

Problem:  Today that connection will not refresh.  It worked just fine three days ago; since then I've revised the query string in Desktop, and upgraded Desktop from the July to August version.  I'm using the same computer with (presumably) the same network settings.

 

I tried to rebuild the connection from BI.com, following the steps above.  This time the connection type was "Office Data Connection".  I pasted my old query string into the command text field, and got the following error:

 

"The connection in this workbook will no longer be identical to the connection defined in the external file located at [file URL].  The link to that external file will also be removed.  Do you want to proceed with changes to the connection in this workbook?"

 

 

I clicked Yes.  I got the error

 

"Excel cannot find OLAP cube evaluate ( summarize ( etc.  Either the OLAP database has been changed or you don't have permission to connect to the cube."

 

Any idea what could have changed?  

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    Hi Anonymous,

     

    It's a cube. As far as I know, we can't send a DAX query to the cube. I would suggest you define these query in the mode before you use them in the Excel.

    Excel_DAX_query_will_not_refresh

     

     

    Best Regards,
    Dale