In this article I'll introduce xlDAX's DAX() function.

DAX()

The 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 ADO.LoadRSfromADO() function)

The function can be called like a normal cell formula, or in VBA via an Application.Run(“DAX”,.. call.

DAX() Signature

DAX(daxQuery,[destinationRange/arg1],[arg2],…,[argN])

...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 arg1.

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.

Examples:

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.)