xlDAX - DAXFILTER()
The DAX Query acted on by the DAX() function is formed using the
:N args, if any, and as such, the various replaceable elements of the Query expression can be distributed in any manner, so:
DAX("EVALUATE :1 :2","Ass","ets")
… would be an odd way to approach the task, but would work :)
But the use case I had in mind was as pointers to selection cells on a report sheet, so:
DAX("EVALUATE ROW(""Discount Qty"",CALCULATE(SUM(Invoices[Qty]),:1,:2))",B2,A3) where: B2 contains Customer[Country]="DE" A3 contains "Invoices[DiscountYN]="Y"
…i.e. a classic pivot report, would be more common.
To see the actual final query after all tokens have been replaced, right-click on the cell and xlDAX->DAX Editor. Or, to just build the query text, but not execute it: add a final argument of __DAX__ to the argument list, this will return the rendered text. These show me the code options also apply to functions that wrap DAX() and to some helpers, such as DAXFILTER (covered below).
To aid this more likely use of an argument list, xlDAX exposes a number of helper functions:
DAXFILTER( filterExpression,[displayText/arg1],[arg2],…,[argN] )
...where filterExpression would normally be in a filter expression as expected by a
CALCULATETABLE expression. This filter text can contain argument tokens in the format :N where N refers to the ordinal of the argument to use. The function returns the value of
arg1 as its display text, not as you might suspect, the token replaced filter text!
So, what use is it?
By itself, no use at all, but if a
DAX() formula’s argument list points at a cell containing a
DAXFILTER() formula it’ll use the rendered filter text (not the display text) as its resolved value, so:
DAXFILTER("Customer[Country]="":1""","DE") …will display: DE …but will return to DAX(): Customer[Country]="DE"
DAXFILTER("Customer[Country]="":2""",,"Germany","DE") …will display: Germany …but will return to DAX(): Customer[Country]="DE"
Okay, you could do this using normal Excel formula, this just makes producing crosstab like reports a bit easier, but you could live without it. The next two helper functions, however, are definitely well worth getting to know, especially if you intend to write VBA functions to test, or act in parallel, with DAX functionality. These are:
I’ll cover them next.