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

[manoj_0911]

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!

[Anonymous · Accepted solution]

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.

[Kedar_Pande]

@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

[Ritaf1983]

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