Forum Discussion
Format function does not support for direct query
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 !!!
I want to use the above lines of code and in the last line, Format function doesn't support a Direct queries.
The only thing at the moment I can imagine is to try changing the formatting of column BMCServiceDesk__TimeSpentInCurrentStatus__c
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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]")
