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:
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 501
Prior 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.