Forum Discussion

Shree_185's avatar
Shree_185
Helper I
4 years ago
Solved

Format function does not support for direct query

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 = va...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
WeeingInTheWind
New Member
3 years ago

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]")