When we use Power BI to connect to a data source such as SQL Server in DirectQuery mode, we’ll find that by default we cannot use the complete range of DAX functions inside our calculations.
Whenever we get an error message like “Function 'XXXXX" is not supported in DirectQuery mode” we know that we’re trying to use a DAX function that isn’t allowed by default in DirectQuery mode.
Why does this limitation exist?
If the Query Editor query is overly complex, an error occurs. To remedy the error, either delete the problematic step in Query Editor , or import the data instead of using DirectQuery.
Time intelligence capabilities are unavailable in DirectQuery. For example, special treatment of date columns (such as year, quarter, month, or day) isn’t supported in DirectQuery mode.
Limitations are placed on DAX expressions allowed in measures to ensure that queries sent to the underlying data source have acceptable performance.
All this limitations exists just to improve the performance reasons. In Power BI, every time we interact with a report (by opening it, clicking on a slicer etc) a series of DAX queries is sent back to the Data Model to get the data needed by the report. In DirectQuery mode, all of these DAX queries – including any calculations – are translated into queries that are sent back to the data source. If that data source is SQL Server, then Power BI generates SQL queries. Not everything that we can do in a DAX calculation can be translated back into efficient SQL, so to prevent us from accidentally building calculations that will perform badly in DirectQuery mode Power BI tries to prevent us from using any DAX functions that it thinks it can’t generate efficient SQL for.
DAX functions in DirectQuery mode
All DAX functions are supported for DirectQuery models. However, not all functions are supported for all formula types, and not all functions have been optimized for DirectQuery models. At the most basic level, DAX functions can be put into two camps: Optimized and Non-optimized. Let’s first take a closer look at optimized functions.
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 measure and query formulas only
This list keeps on changing from time-to-time with each new release.
Some 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, albeit with uncertain performance.
The reasons a particular function might not be optimized for DirectQuery is because the underlying relational engine cannot perform calculations equivalent to those performed by the VertiPaq engine, or the formula cannot be converted to an equivalent SQL expression. In other cases, the performance of the converted expression and the resulting calculations may be unacceptable.
And lastly if you visit the Page 10, 20, 21 and 22 from the PDF which you’ve provided in that you’ll find an article related to the Calculated Tables and Columns stating as follows -
“Calculated tables — DirectQuery does not support calculated tables, mainly because there is no place to store them.”
Hoping you find this useful and can help you to achieve the desired analysis.
Please feel free to write back in case I’ve missed out on anything.
Thanks & Warm Regards,