In the past I’ve demonstrated a few examples of using DAX Query in PowerPivot workbooks, this “create a Date Dim” for example.
The ability to produce tables rather than just pivots can be very useful; not just for ETL-like activities like the above mentioned Date Dim, but also for testing and development; and for producing pivot friendly flattened tables for consumption by other other data vsiualisation toolsets.
I’ve been asked a few times what’s the best way to learn DAX Query? Well, in most respects, it’s the same as the standard DAX used to build measures and calculated columns, so just keep practicing that measure building!
But, another interesting way, is to see the DAX auto-created by a tool that, I suspect, was the original use-case for tabular DAX i.e. PowerView.
PowerView comes with Excel 2013 (at least those versions that also pack the PowerPivot add-in) so you no longer require a SharePoint farm to try it out.
(One thing to note if you’re running a 64bit version of Excel 2013, you’ll need to source a 64 bit version of Sliverlight; the 32bit download that you’ll be prompted to install via your (32bit) browser will not work.)
Here’s a good introduction to report building in PowerView by MSBI Academy.
To see the DAX generated by PowerView (and indeed the MDX generated by the workbook’s pivotables) use the DMV view DISCOVER_COMMANDS.
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.
Note: you’ll need a true external connection (a workbook table will not enable the Edit DAX option). Also, the table’s connection points at the model, so be careful with the Refresh command (i.e it’ll cause the table to refresh). It’s a little bit more complex, it depends on the nature of the DAX; the engine uses a dependency graph it seems (which is good, shows this feature is fully integrated). If your DAX mentions a column on a table, then that table will be refreshed.
The Edit Dax action uses a…
Connections(“ModelConnection_<<whatever>>”).Refresh
…which is obviously only between workbook and model, so doesn’t cause this external refresh.
You may also notice that sometimes the sequence of columns returned is different from expected, I usually find re-issuing the DAX command fixes this.
Because of the awkwardness of the above hack(s) when I don’t need this dependency stuff I usually revert to my HAMMER micro-ETL utility to do any DAX, MDX or DMV queries via the HAMMER(“PPCONECTION”, <<my command>>, “ADO”) command set.
You could also write your own ADO code, latching on to the ActiveWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection connection object.
Not only can you see examples of DAX Query construction using the above methods, but you can also run/modify the extracted commands using the same.
Another very useful use of DISCOVER_COMMANDS is to pick-up the MDX DRILLTHROUGH command issued when a Show Details is applied to a pivottable cell.
You can then, for example, change the MAXROWS returned (something that disappeared in 2013, only other method is to open the workbook ZIP source and modify the Connection’s XML).
But even better, you can pick a different set of columns to return. This is so useful, as normally the default DRILLTHOUGH usually doesn’t bring back useful columns such as the row’s unique ID!
See here for a workbook containing examples of the above.
To use the HAMMER commands you’ll need to manually open the xll (make sure to pick the correct bitness).
To enable HAMMER’s full functionality (namely any command, such as HAMMERtoFit, that uses a background thread) you’ll need to close and reopen Excel. This is something new in Excel 2013, probably related to ensuring that an internet-delivered workbook doesn’t use something like a background thread to do something nasty before it has been “trusted”. Well that’s my theory anyway, and I’m sticking to it. Anybody else seen similar?
UPDATE: Another method to have fun with DAX Query expressions is Dax Studio, which now supports Excel 2013! http://geekswithblogs.net/darrengosbell/archive/2013/05/04/new-release—dax-studio-1.2-with-excel-2013-support.aspx
Gobán Saor 