Forum Discussion

manoj_0911's avatar
manoj_0911
Icon for Kudo Commander rankKudo Commander
1 year ago
Solved

Alternatives to FORMAT Function for Date-Time Formatting in DirectQuery Mode

Hi,

 

I’m currently working with a set of reports that use **Import mode**, but I want to convert these to **DirectQuery mode** for testing purposes. However, I’m encountering issues with date-time formatting when I switch to DirectQuery.

 

In Import mode, I used the `FORMAT` function in calculated columns to format date-time values, but in DirectQuery, I receive the following error:

 

> *"Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models."*

 

Here are the calculated columns I’m attempting:

1. **Date:** `FORMAT('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "yyyy-MM-dd")`
2. **Hour:** `FORMAT('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "yyyy-MM-dd HH:00:00")`
3. **Month:** `FORMAT('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "MMMM yyyy")`
4. **Subhour:** `FORMAT('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "yyyy-MM-dd HH") & IF(MINUTE('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]) < 30, ":00:00", ":30:00")`

 

Since `FORMAT` isn’t supported in DirectQuery calculated columns, I’d like advice on any workarounds.

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi manoj_0911 

     

    Thank you very much Ritaf1983 and Kedar_Pande for your prompt reply.

     

    Pls try this:

     

     

    Here's some dummy data

     

     

    Create a measure.

     

    Measure = 
VAR _date = SELECTEDVALUE('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE])
RETURN
TIMEVALUE(_date)

     

    Here is the result.

     

     

    Try to convert a text type to a date/time type in DirectQuery mode using TIMEVALUE().

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

3 Replies

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

    Hi manoj_0911 

    When switching from Import mode to DirectQuery, Power BI restricts certain functions like FORMAT in calculated columns. Here are some workarounds for each case, focusing on using FORMAT alternatives or moving these transformations to Power Query or your source database.

    Workarounds:
    Date Format: Instead of FORMAT('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "yyyy-MM-dd"), you can use:

    DAX
    Copy code
    YEAR('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]) & "-" &
    RIGHT("0" & MONTH('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2) & "-" &
    RIGHT("0" & DAY('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2)
    This concatenates year, month, and day as strings without FORMAT.

    Hour Format: Replace FORMAT('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "yyyy-MM-dd HH:00:00") with:

    DAX
    YEAR('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]) & "-" &
    RIGHT("0" & MONTH('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2) & "-" &
    RIGHT("0" & DAY('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2) & " " &
    RIGHT("0" & HOUR('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2) & ":00:00"
    Month Format: For the month format FORMAT('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "MMMM yyyy"), one workaround is to use:

    DAX
    FORMAT( 'QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE], "MMMM" ) & " " &
    YEAR( 'QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE] )
    Alternatively, move this to Power Query or your source database if the above approach doesn’t work due to DirectQuery restrictions.

    Subhour Format: To replace FORMAT for subhour, you can use:

    DAX
    YEAR('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]) & "-" &
    RIGHT("0" & MONTH('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2) & "-" &
    RIGHT("0" & DAY('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2) & " " &
    RIGHT("0" & HOUR('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]), 2) &
    IF(MINUTE('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE]) < 30, ":00:00", ":30:00")
    Alternative Approach: Using Power Query or the Source Database
    For better performance and less complexity, consider pushing these transformations to Power Query or directly to your source database:

    In Power Query, you can format dates and times using M language without DirectQuery limitations.
    In the source database, use SQL formatting functions (like CONVERT or FORMAT) to handle date-time formatting before it reaches Power BI

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

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

    manoj_0911 

    Consider creating date-time columns in your source database to achieve the formatting before it’s imported into Power BI. This approach avoids the need for complex DAX altogether, which is especially useful in DirectQuery mode.

     

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi manoj_0911 

     

    Thank you very much Ritaf1983 and Kedar_Pande for your prompt reply.

     

    Pls try this:

     

     

    Here's some dummy data

     

     

    Create a measure.

     

    Measure = 
VAR _date = SELECTEDVALUE('QUEUE_ABN_SUBHOUR_V'[IXN_SUBHOUR_DATE])
RETURN
TIMEVALUE(_date)

     

    Here is the result.

     

     

    Try to convert a text type to a date/time type in DirectQuery mode using TIMEVALUE().

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.