Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.