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:

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.

DAX Syntax Reference

DAX Syntax Reference.

DAX formula compatibility in DirectQuery mode

DAX formula compatibility.