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.

Slicers and Drill-through in Excel.

While I was playing around with Excel 2013, I noticed some inconsistency in the way slicers behave depending on whether they are used to filter a PowerPivot table, or whether they are used to filter a traditional pivot table.

With traditional pivot tables, slicers do not affect drill-through if they are applied on hidden fields. In other words, if you use a slicer to filter Field1, then you must place Field1 somewhere in your table (row, column, or page) in order for the drill-through to behave as expected.

This is not the case with PowerPivot tables. Drill-through works as expected.

What’s new for Excel 2013 – Personal observations

The customer preview of Office 2013 is finally available, and there is a lot of new things to look at, be it for users or for developers.

Here are a few personal observations based on my first look at the product.

  • PowerPivot for Office 2013  now comes bundled with Excel, making Office On Demand, the evolution of the Click-To-Run technology, much more viable.
  • The PowerPoint add-in is no longer required to build simple data models and pivot tables based on several data tables. Measures, KPIs and some other features will require the add-in to be activated.
  • The message “To use multiple table in your analysis, a new PivotTable needs to be created based on the Data Model.” hints at the fact that old-school pivot tables are not dead yet and still are the default PivotTable type.
  • New table objects in your workbook will automatically be added to your data model.
  • You can create a pivot table based on a simple range and then transform the pivot table into a “PowerPivot model”. The initial range will not be transformed into a table object. Further ranges can be added through the Connections manager. (What for?)
  • The data model can now be accessed from VBA. The corresponding object is called Model (not DataModel), despite what the MSDN documentation mentions about it.
  • The PowerPivot add-in will not recognize tables that were added directly to the data model from Excel as linked tables. However, a new connection will be created in Excel, so that refactoring can be done from the Connections manager. A corresponding object called WorksheetDataConnection is available in VBA.
  • You can create two worksheet data connections for the same range. Duplicating ranges for scenarios where the same data must be used twice (for example, when a dimension must play different roles) is no longer required.
  • The function FILTERXML allows you to query an XML document with XPath. The WEBSERVICE function returns an XML text from a web service. ENCODEURL is a helper function to encode a string into a valid URL. All three functions are available in VBA through the WorksheetFunction object.
  • The function FILTERXML returns an array. You can use it in array formulas, or in conjunction with the INDEX function, for example.
  • New functions like SHEET, and SHEETS are available, although it is still unclear to me in which scenarios they will be useful, since no specific function takes a sheet index as a parameter.
  • ISFORMULA and FORMULATEXT are also new in Excel 2013.
  • Examples in the help system are provided as embedded Excel Web App workbooks. This may solve some translation issues that have occurred in the past for non-English Office versions.
  • Internet Explorer is used to navigate the VBA help. You can now alt-tab between the VBA environment and the documentation.

Downloading multiple files from Skydrive

I have just downloaded a few PowerPoint sample files from Skydrive.

To keep track of the downloaded files, I created a folder, as I usually do.

You know what? I did not need to do that.

Skydrive creates a single zip archive containing all the selected files.

A simple but very user-friendly idea. Brilliant!

%d bloggers like this: