Format function does not support for direct query

[Shree_185]

Hi, I need your help to I how to replace the format function in the Direct query. In the last line, the Format function does not support for SQL SERVER DIRECT query.

Pending Customer in Days = var dayNo=INT([BMCServiceDesk__TimeSpentInCurrentStatus__c]/1440)

var hourNo=INT(MOD([BMCServiceDesk__TimeSpentInCurrentStatus__c],1440)/60)

var minuteNO=MOD(MOD([BMCServiceDesk__TimeSpentInCurrentStatus__c],1440),60)

var secondNo=INT(([BMCServiceDesk__TimeSpentInCurrentStatus__c]-INT([BMCServiceDesk__TimeSpentInCurrentStatus__c]))*60)

return

dayNo&" day "&FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")

[ALLUREAN]

Hi, @Shree_185 

If I understand correctly, these are variables that you want to change format?

Option 1: Try to create different measure for each variable and the change the format of the measure and use it

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

[Shree_185]

I want to use the above lines of code and in the last line, Format function doesn't support a Direct queries.

[Anonymous · Accepted solution]

Hi @Shree_185 ,

Format function is not supported in Direct Query mode in Power BI. You need to convert your connection mode to import to use it.

Or I suggest you to create two calcualted columns, one to show Days and another one to show times in time type formated as hh:mm:ss. 

Pending Customer in Days = 
VAR dayNo =
    INT ( [BMCServiceDesk__TimeSpentInCurrentStatus__c] / 1440 )
RETURN
    dayNo & " day "

Pending Customer in Times = 
VAR hourNo =
    INT ( MOD ( [BMCServiceDesk__TimeSpentInCurrentStatus__c], 1440 ) / 60 )
VAR minuteNO =
    MOD ( MOD ( [BMCServiceDesk__TimeSpentInCurrentStatus__c], 1440 ), 60 )
VAR secondNo =
    INT (
        (
            [BMCServiceDesk__TimeSpentInCurrentStatus__c]
                - INT ( [BMCServiceDesk__TimeSpentInCurrentStatus__c] )
        ) * 60
    )
RETURN
TIME(hourNo,minuteNO,secondNo)

Result is as below.

Best Regards,
Rico Zhou

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

[WeeingInTheWind]

From the Excel client with a pivot table against a PowerPivot model connected to a SQL backend, I wanted to use ManagedSets to create an MDX statement like the one below (which works). When I try and format the date to make it dynamic with now(), the format function is not known. Hence, I assume this is what this thread is about. So, after all these years, still, no decent way to get a named set out of a power pivot model with date math?

StrToMember("[Calendar].[Calendar Date].[2023-06-25]")
:
StrToMember("[Calendar].[Calendar Date].[2023-06-29]")

[ALLUREAN]

The only thing at the moment I can imagine is to try changing the formatting of column BMCServiceDesk__TimeSpentInCurrentStatus__c