Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
01-21-2018 11:26 AM - last edited 06-27-2018 13:46 PM
Converts a Text duration in the format HH:MM:SS to seconds. The measure takes a single input column ([Duration]).
Duration2Seconds =
VAR sub = SUBSTITUTE(MAX('Table'[Duration]),":","|")
VAR hours = VALUE(PATHITEM(sub,1))*3600
VAR minutes = VALUE(PATHITEM(sub,2))*60
VAR seconds = VALUE(PATHITEM(sub,3))*1
RETURN hours+minutes+seconds
With the appropriate wizard interface and abstraction, this technique could handle any number of levels and a variety of data input formats, as long as those formats are segmented by a common delimiter ([Delimiter]). The interface could parse the string and present an interface for entering the correct conversion factor ([CF#]) for each level. Pseudo-code follows:
Conversion =
VAR sub = SUBSTITUTE(MAX('Table'[Column]),"[Delimiter]","|")
VAR level1 = VALUE(PATHITEM(sub,1))*[CF1]
VAR level2 = VALUE(PATHITEM(sub,2))*[CF2]
VAR level3 = VALUE(PATHITEM(sub,3))*[CF3]
...
VAR level# = VALUE(PATHITEM(sub,#))*[CF#]
RETURN level1+level2+level3+...+level#
eyJrIjoiZDg1YThlNWItYWI3MC00ZjA2LTk1YTItOTM4NzgxOGYxMmJkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Hi I tried this method it didn't work for me. Says variabl 'minutes' cannot be created because a table or variable with the same name already exist
I did some Study it seems i was able to Substitute the space with ":", however, 00:00:14:26 = 14 mins roughly, the problem is for level 1 it is using from 14 not using 00
I am Having DD HH:MM:SS , could you help me in figuring it