Connect to dataset with analise in excel, then access data with dax query

[RobertSlattery]

When a workbook is connected to a dataset using pbiazure://api.powerbi.com, I can consume the dataset data model in pivot tables.  How can I connect a DAX query to it and create a flat table in the spreadsheet?  Is there a way to access the table in the dataset in a DA query?

[RobertSlattery · Accepted solution]

Solution here.

[lbendlin]

Use whatever XMLA endpoint software you like (for example DAX studio or SSMS), connect to the dataset, and run your queries. 

Keep in mind that XMLA endpoint gives you access to the entire data model in the dataset.  Trying to create a flat table for a spreadsheet from that could be considered a large step backwards.

[RobertSlattery]

Yep, I am aware of those options thanks.  My requirement is to do it using excel as the client.  It seems like this is not possible?

[lbendlin]

No, Excel does not allow you to run DAX queries as far as I know. But you be should be able to pivot the data into a close approximation. Worst case you could attack the dataset (as as SSAS data source) with Power Query. But that is even more questionable.

[RobertSlattery]

Excel has the capability to run DAX queries on the data model in the spreadsheet, I do it all the time.  This is a great way to transform and visualise the data with the full power of DAX queries, without the hoops you have to jump through, or the visual comprimises you have to make with pivot tables.  I am not talking about EVALUATE 'Table Name' to simply dump tables from the model on to the sheet surface.  Not sure why this would be "questionable".  Bringing a table into the local model with power query on the other hand: agree, this is not good architecture.  Hence my question.