DAX Queries in Excel 2013

The “old” drill-through trick that could be used in Office 2010 and PowerPivot v2 no longer works in Excel 2013. You can no longer edit the connection for the resulting table.

Luckily, this is no longer required.

If you want to include a DAX query based on the model in your workbook, you can proceed as follows:

• Go to Data > Existing Connections (EDIT: from the regular Excel ribbon, not the PowerPivot window.)
• Select a table
• When asked how you want to view your data, select “Table”
• To edit the DAX query, right click on the table, then choose Table > Edit DAX …

If, like me, you get the error message “query could not run or the data model could not be accessed. Query (row, position) cannot reference a base table in the EVALUATE query, because it is undetermined”, check whether your query is correct.

The following query, for example, would run smoothly for the appropriate table.

EVALUATE
SUMMARIZE(Table,
ROLLUP( Table[Category],
Table[Subcategory]),
“Sales”, COUNTA( Table[Subcategory])

)

Note that in this mode, the EN-US locales seem to be in use.

Finally, you can reuse the table in your model, if you wish. I have not tested how this affects the updating of the model, though.