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.

About these ads

5 Responses to “DAX Queries in Excel 2013”

  1. DAX Queries in Excel 2013 : using ROLLUP to cover some UNION scenarios « The Data Specialist Says:

    [...] DAX Queries in Excel 2013 [...]

  2. Cross Join Three Tables via DAX Query to seed a Date Dimension. | Gobán Saor Says:

    [...] You can apply the above DAX query using this trick (Note: this will not work for Excel 2013, but no worries, as  in Excel 2103, DAX Queries are a supported data-table source, see here). [...]

  3. extracting powerpivot data to excel using evaluate Says:

    [...] just found this link that explains how to do it, but doesn't make any sense to what I see DAX Queries in Excel 2013 | The Data Specialist He says select connection and then a table, but I only see a list of tables that I have linked [...]

    • TheDataSpecialist Says:

      Selecting ANY table should be ok, since you will write a DAX query anyway.

  4. DISCOVER_COMMANDS DMV – Improve Drillthrough & Learn DAX Query | Gobán Saor Says:

    [...] For Excel 2010 you would have used my trick to fire DMV views, but this no longer works in 2013. So, instead, you can use this trick to execute any DAX, MDX or DMV. [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: