Formula Compatibility in DirectQuery Mode
The Data Analysis Expression language (DAX) can be used to create measures and other custom formulas that you use in tabular models and in PowerPivot workbooks. In almost every respect, the models that you create in these two environments are identical, and you can use the same measures, relationships, and KPIs, etc. However, if you build a tabular model and deploy it in DirectQuery mode, there are some restrictions on the formulas that you can use. This topic provides an overview of the differences, lists the functions that are not supported in DirectQuery mode, and lists the functions that are supported but might return different results.
Within this topic, we use the term in-memory model to refer to both PowerPivot models, which use a local cache, as well as tabular models, which are fully hosted in memory data on an Analysis Services server running in Tabular mode. We use DirectQuery models to refer to tabular models that have been deployed in DirectQuery mode. For information about DirectQuery mode, see DirectQuery Mode (SSAS Tabular).
Functions that are not in either of these lists are expected to behave identically regardless of the model storage or query mode.
Queries on a model deployed in DirectQuery mode can return different results than when the same model is deployed in-memory, because data is fetched directly from a relational data store and aggregations required by formulas are performed using the relevant relational engine, rather than using the xVelocity in-memory analytics engine (VertiPaq) for storage and calculation.
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 for evaluation, the data must be handled according to the semantics of the relational data source, which typically require distinct handling of empty strings vs. nulls. For this reason, the same formula might return a different result when evaluated against cached data and against data fetched solely from the relational store.
Additionally, some functions cannot be used at all in DirectQuery mode because the calculation would require that the data in the current context be sent to the relational data source as a parameter. For example, measures in a PowerPivot workbook often use time intelligence functions that reference date ranges available within the workbook. Such formulas generally cannot be used in DirectQuery mode.
This section lists the types of semantic differences that you can expect, and describes any limitations that might apply to the usage of functions or to query results.
Comparisons
DAX in in-memory models supports comparisons of two expressions that resolve to scalar values of different data types. However, models that are deployed in DirectQuery mode use the data types and comparison operators of the relational engine, and therefore might return different results.
The following comparisons will always generate an error when used in a calculation on a DirectQuery data source:
-
Numeric data type compared to any string data type
-
Numeric data type compared to a Boolean value
-
Any string data type compared to a Boolean value
In general, DAX is more forgiving of data type mismatches in in-memory models, and will attempt an implicit cast of values up to two times, as described in this section. However, formulas sent to a relational data store in DirectQuery mode are evaluated more strictly, following the rules of the relational engine, and are more likely to fail.
Casts
There is no cast function as such in DAX, but implicit casts are performed in many comparison and arithmetic operations. It is the comparison or arithmetic operation that determines the data type of the result. For example,
-
Boolean values are treated as numeric in arithmetic operations, such as TRUE + 1, or the function MIN applied to a column of Boolean values. A NOT operation also returns a numeric value.
-
Boolean values are always treated as logical values in comparisons and when used with EXACT, AND, OR, &&, or ||.
Math Functions and Arithmetic Operations
Some mathematical functions will return different results in DirectQuery mode, because of differences in the underlying data type or the casts that can be applied in operations. Also, the restrictions described above on the allowed range of values might affect the outcome of arithmetic operations.
Supported Numeric and Date-Time Ranges
Formulas in PowerPivot and tabular models in memory are subject to the same limitations as Excel with regard to maximum allowed values for real numbers and dates. However, differences can arise when the maximum value is returned from a calculation or query, or when values are converted, cast, rounded, or truncated.
-
If values of types Currency and Real are multiplied, and the result is larger than the maximum possible value, in DirectQuery mode, no error is raised, and a null is returned.
-
In in-memory models, no error is raised, but the maximum value is returned.
In general, because the accepted date ranges are different for Excel and SQL Server, results can be guaranteed to match only when dates are within the common date range, which is inclusive of the following dates:
-
Earliest date: March 1, 1900
-
Latest date: December 31, 9999
If any dates used in formulas fall outside this range, either the formula will result in an error, or the results will not match.
Currency
In DirectQuery mode, if the result of an arithmetic operation has the type Currency, the value must be within the following range:
-
Minimum: -922337203685477.5808
-
Maximum: 922337203685477.5807
Aggregation Functions
Statistical functions on a table with one row return different results. Aggregation functions over empty tables also behave differently in in-memory models than they do in DirectQuery mode.
Text Functions
Because relational data stores provide different text data types than does Excel, you may see different results when searching strings or working with substrings. The length of strings also can be different.
In general, any string manipulation functions that use fixed-size columns as arguments can have different results.
Additionally, in SQL Server, some text functions support additional arguments that are not provided in Excel. If the formula requires the missing argument you can get different results or errors in the in-memory model.
The following DAX functions can be used in DirectQuery mode, but with the qualifications as described in the preceding section.
Text functions
CONCATENATE
FIND
LEFT
LEN
MID
REPLACE
REPT
RIGHT
SUBSTITUTE
TRIM
Statistical functions
COUNT
STDEV.P
STDEV.S
STDEVX.P
STDEVX.S
VAR.P
VAR.S
VARX.P
VARX.S
Date/time functions
DATE
EDATE
EOMONTH
DATE
TIME
SECOND
Math and number functions
CEILING
LN
LOG
LOG10
POWER
DAX Table queries
There are some limitations when you evaluate formulas against a DirectQuery model by using DAX Table queries. DirectQuery does not support referring to the same column twice in an ORDER BY clause. The equivalent Transact-SQL statement cannot be created and the query fails.
In an in-memory model, repeating the ORDER by clause has no effect on the results.
Some DAX functions are not supported in models that are deployed in DirectQuery mode. The reasons that a particular function is not supported can include any or a combination of these reasons:
-
The underlying relational engine cannot perform calculations equivalent to those performed by the xVelocity engine.
-
The formula cannot be converted to en equivalent SQL expression.
-
The performance of the converted expression and the resulting calculations would be unacceptable.
The following DAX functions cannot be used in DirectQuery models.
Path functions
PATH
PATHCONTAINS
PATHITEM
PATHITEMREVERSE
PATHLENGTH
Misc functions
COUNTBLANK
FIXED
FORMAT
RAND
RANDBETWEEN
Time intelligence functions: Start and end dates
DATESQTD
DATESYTD
DATESMTD
DATESQTD
DATESINPERIOD
TOTALMTD
TOTALQTD
TOTALYTD
DATESINPERIOD
SAMEPERIODLASTYEAR
PARALLELPERIOD
Time intelligence functions: Balances
OPENINGBALANCEMONTH
OPENINGBALANCEQUARTER
OPENINGBALANCEYEAR
CLOSINGBALANCEMONTH
CLOSINGBALANCEQUARTER
CLOSINGBALANCEYEAR
Time intelligence functions: Previous and next periods
PREVIOUSDAY
PREVIOUSMONTH
PREVIOUSQUARTER
PREVIOUSYEAR
NEXTDAY
NEXTMONTH
NEXTQUARTER
NEXTYEAR
Time intelligence functions: Periods and calculations over periods
STARTOFMONTH
STARTOFQUARTER
STARTOFYEAR
ENDOFMONTH
ENDOFQUARTER
ENDOFYEAR
FIRSTDATE
LASTDATE
DATEADD