Power BI - DAX
Overview
When DirectQuery is enabled, DAX functions are mostly supported but not all functions are supported for all formula types, and not all functions have been optimized for Direct Query models.
Optimized for DirectQuery
These are functions that primarily return scalar or aggregate results. These functions are further divided into those that are supported in all types of formulas: measures, queries, calculated columns, row level security, and those that are supported in measure and query formulas only. These include:
Supported in all DAX formulas
Supported in all DAX formulas
Supported in measure and query formulas only
ABS
MINUTE
ALL
ACOS
MOD
ALLEXCEPT
ACOT
MONTH
ALLNOBLANKROW
AND
MROUND
ALLSELECTED
ASIN
NOT
AVERAGE
ATAN
NOW
AVERAGEA
BLANK
OR
AVERAGEX
CEILING
PI
CALCULATE
CONCATENATE
POWER
CALCULATETABLE
COS
QUOTIENT
COUNT
COT
RADIANS
COUNTA
CURRENCY
RAND
COUNTAX
DATE
RELATED
COUNTROWS
DATEDIFF
REPT
COUNTX
DATEVALUE
RIGHT
DISTINCT
DAY
ROUND
DISTINCTCOUNT
DEGREES
ROUNDDOWN
FILTER
DIVIDE
ROUNDUP
FILTERS
EDATE
SEARCH
HASONEFILTER
EOMONTH
SECOND
HASONEVALUE
EXACT
SIGN
ISCROSSFILTERED
EXP
SIN
ISFILTERED
FALSE
SQRT
MAXA
FIND
SQRTPI
MAXX
HOUR
SUBSTITUTE
MIN
IF
SWITCH
MINA
INT
TAN
MINX
ISBLANK
TIME
RELATEDTABLE
ISO.CEILING
TIMEVALUE
STDEV.P
KEEPFILTERS
TODAY
STDEV.S
LEFT
TRIM
STDEVX.P
LEN
TRUE
STDEVX.S
LN
TRUNC
SUM
LOG
UNICODE
SUMX
LOG10
UPPER
VALUES
LOWER
USERNAME
VAR.P
MAX
USERELATIONSHIP
VAR.S
MID
VALUE
VARX.P
MIN
WEEKDAY
WEEKNUM
YEAR
Non-optimized for DirectQuery
These functions have not been optimized to work with DirectQuery. These functions are not supported in calculated column and row-level security formulas at all. However, these functions are supported in measure and query formulas, although with uncertain performance. Any function absent from one of the lists of optimized functions above is a non-optimized function for DirectQuery.
Such functions are declared as 'not optimized' because PowerBI registers that the underlying relational engine can perform calculations equivalent to those performed by the xVelocity engine, or because the formula cannot be converted to an identical SQL expression.
Example of issue for non optimized functions
INDEXIMA is basically an HIVE2 server with a set of HSQL command fully compatible APACHE HIVE.
When a data type of a HIVE dataspace is DATE, MS Power BI generates a query where aliases are defined inside an inner SELECT and are used by the GROUP BY or the ORDER BY in the outer SELECT as shown in the following query:
text
SELECT `my_date`
FROM (
SELECT `my_date`, `C1`,
CASE
WHEN `C1` IS NOT null THEN CAST(`C1` AS TIMESTAMP)
ELSE { ts '1899-12-28 00:00:00' }
END AS `C2`,
CASE
WHEN `C1` IS null THEN 0
ELSE 1
END AS `C3`
FROM (
SELECT `my_date`, `my_date` AS `C1`
FROM `pbi_test`
) AS `ITBL`
GROUP BY `my_date`, `C1`
) AS `ITBL`
ORDER BY `C2`, `C3`
LIMIT 501Prior version 1.7.0, INDEXIMA CLUSTER responds to such query with the error C2, C3, C... column not found. This is clearly not an HIVE compatible query. Since INDEXIMA version 1.7.0, such query is interpreted as a pure SQL-92 query to allows MS Power BI to retrieve all date information
Mode in-memory vs DirectQuery
Queries on a model deployed 'in DirectQuery mode' return different results than queries on a model deployed 'in-memory mode'.
This is because with DirectQuery, data is queried directly from a data source and aggregations required by formulas are performed using the relevant database engine (SQL, INDEXIMA,...), rather than using the xVelocity in-memory analytics engine.
For example, there are differences in the way that certain relational Data Stores handle numeric values, dates, nulls, and so forth.
In contrast, the DAX language is intended to emulate as closely as possible the behavior of functions in Microsoft Excel. For example, when handling nulls, empty strings and zero values, Excel attempts to provide the best answer regardless of the precise data type, and therefore the xVelocity engine does the same. However, when a tabular model is deployed in DirectQuery mode and passes formulas to a relational data source, the data must be handled according to the semantics of the data source.
Additionally, some functions aren't optimized for DirectQuery mode because the calculation would require the data in the current context be sent to the relational data source as a parameter. For example, measures using time-intelligence functions that reference date ranges in a calendar table.
Microsoft References
DAX functions in DirectQuery mode
Microsoft reference for DAX functions; DAX Function Reference.
DAX operators in DirectQuery mode
All DAX comparison and arithmetic operators are fully supported in DirectQuery mode. Microsoft reference for DAX operator; DAX Operator Reference.