Forum Discussion

pennyhoho117's avatar
pennyhoho117
Icon for Helper IV rankHelper IV
1 year ago
Solved

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

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?
Need Help
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    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's avatar
    v-hashadapu
    1 year ago

    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.

9 Replies

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      pennyhoho117
      Icon for Helper IV rankHelper IV
      1 year ago

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

      • v-hashadapu's avatar
        v-hashadapu
        Icon for Community Support rankCommunity Support
        1 year ago

        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.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    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.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    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.