Why some function such as DATE is not allowed as part in DAX expression on DirecQuery model

[pennyhoho117]

i write a calculate column as below:

last_es_call_log_date =

 VAR DateValue =es_request_loans[esn.send_notification_at] + TIME(8,0,0)

 RETURN

 DATE(YEAR(DateValue), MONTH(DateValue),DAY(DATEVALUE))

but return with error message:

Query(2,2) Function DATE is not allowed as part of calculated column DAX expression on DirecQuery model

Can any people tell me why?

[Poojara_D12 · Accepted solution]

Hi @pennyhoho117 

The error occurs because DirectQuery mode in Power BI has restrictions, and the DATE function cannot be used in calculated columns. DirectQuery pushes calculations to the data source, and some DAX functions (like DATE, TIME, DATEVALUE) cannot be translated into SQL queries.

please try the dax:

last_es_call_log_date = 
VAR DateValue = es_request_loans[esn.send_notification_at] + TIME(8, 0, 0)
RETURN INT(DateValue)

The best approach is to compute this column in your data source to keep the model in DirectQuery mode.

 

[v-hashadapu]

Hi @pennyhoho117 , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @pennyhoho117 , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @pennyhoho117 , Hope your issue is solved. If it is, please consider marking it 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details. Thank you.

[Poojara_D12 · Accepted solution]

Hi @pennyhoho117 

The error occurs because DirectQuery mode in Power BI has restrictions, and the DATE function cannot be used in calculated columns. DirectQuery pushes calculations to the data source, and some DAX functions (like DATE, TIME, DATEVALUE) cannot be translated into SQL queries.

please try the dax:

last_es_call_log_date = 
VAR DateValue = es_request_loans[esn.send_notification_at] + TIME(8, 0, 0)
RETURN INT(DateValue)

The best approach is to compute this column in your data source to keep the model in DirectQuery mode.

 

[v-hashadapu]

Hi @pennyhoho117 , thank you for reaching out to the Microsoft Fabric Community Forum.

1. Direct Query has restrictions on some DAX functions to ensure queries are compatible with the underlying data source. This includes Date function.
2. One can restructure their calculated column formula to adhere to DirectQuery limitations, to avoid using unsupported functions.
3. Here is a modifed version of your DAX expression:

last_es_call_log_date =

VAR DateValue = es_request_loans[esn.send_notification_at] + TIME(8, 0, 0)

RETURN

DATEVALUE(DateValue)

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[pennyhoho117]

Besides Date function, may i know the DAX function/ format fields/M functions list that not allowed in Direct Query? Thanks

[v-hashadapu]

Hi , thank you for reaching out to the Microsoft Fabric Community Forum.

Below are some of the functions that do not work in Direct Query mode.

DATE Function: This function is not allowed in Direct Query mode.

PATH Function: This function cannot be used in Direct Query models.

LOOKUPVALUE Function: Not supported in Direct Query.

SUMX Function: Not supported in Direct Query.

CALCULATE Function: Limited support in Direct Query.

EARLIER Function: Restricted in Direct Query.

FILTER Function: Limited support in Direct Query.

ALL Function: Restricted in Direct Query.

ALLSELECTED Function: Restricted in Direct Query.

ALLEXCEPT Function: Restricted in Direct Query.

 If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[pennyhoho117]

any power bi documnet listed all Dax, M function that not allowed in Direct query?

[v-hashadapu · Accepted solution]

Hi @pennyhoho117, thank you for reaching out to the Microsoft Fabric Community Forum.

There isn't any official list detailing every unsupported function, but several commonly used functions are known to have restrictions or are unsupported in DirectQuery mode.

Please check out the below document for your reference:

DirectQuery in Power BI - Power BI | Microsoft Learn

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.