xlDAX - DAX Function
In this article I'll introduce xlDAX's DAX() function.
DAX() function is the core function within xlDAX, both as a public function and as the end point for many other wrapper functions or utilities (e.g. DAX Editor). In essence, it opens an ADO Connection to the workbook’s model like so …
If CLng(Application.version()) > 14 Then ActiveWorkbook.Model.Initialize Set conn = ActiveWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection Else ActiveWorkbook.Connections("PowerPivot Data").OLEDBConnection.MakeConnection Set conn = ActiveWorkbook.Connections("PowerPivot Data").OLEDBConnection.ADOConnection End If
… and then executes the passed in DAX (or DMV, or MDX) against that connection.(The logic for this resides in the lower-level
The function can be called like a normal cell formula, or in VBA via an
...where daxQuery is a DAX Query (or MDX, or DMV query). The query text can optionally contain argument tokens in the format :N where N refers to the ordinal of the argument to use.
So, if the query text is …
DEFINE MEASURE 'Assets'[noofrows] = COUNTROWS( Filter( Assets, [Fund]=":1") ) EVALUATE CALCULATETABLE( ROW( "CountRows", [noofrows]) )
:1 would be replaced by the text value of
If the token
:1 is not used in a query string, then the position of arg1 can be used to specify a destination range for the resulting table. Note: the range must be specified in textual form not pointed at via a range assignment, so:
"Sheet1!a10" (top left hand corner of the resulting table will be placed a cell A1 on Sheet3) or …
"myTable[#All]", the table named myTable will replaced with the resulting table.
DAX("EVALUATE :1","Assets") DAX("EVALUATE :2","Sheet3!A1","Assets") – will output to range as :2 takes place of :1 DAX("EVALUATE Assets","Sheet3!A1")
If an error:
The error text will be returned. However, if Excel 2013, the only error info you’ll receive is a useless generic top-level
ADO Error #-1055719414 message (Note: Excel 2010 will give a proper error). This seems to be due to Excel 2013’s hacking of the ADO object to support native Power Pivot models, the hack seems to have broken the ADO error stack.
To get around this, you can place the HAMMER add-in alongside xlDAX and it’ll then execute the DAX text via ADOMD’s .NET-only interface which gives back a proper error message.
If a successful DAX Query execution:
The function returns a scalar value (i.e. the top-left-hand cell, row 1, column 1, of the result table), which is fine if testing say a measure like the
Asset[noofrows] example above, but to return a full table, the second destinationRange argument must be specified. (For VBA programmers wishing to process the result table within code the lower-level
ADO. LoadRSfromADO() is the function to use – it and other useful
ADO. functions will be covered later on.)