Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

dax

Hi,   Convert HH:MM:SS to Minutes format and Minutes to HH:MM:SS Duartion format.   Example: 452:55:60 to Minutes   Regards.
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    You need to create 3 calculated columns to get the value of “HH”,”MM”and “SS” first, the related dax expressions are listed below:

     

     

    HH = IF('Sheet1'[Position 1]=0,BLANK(),LEFT('Sheet1'[Resolution Time (H:M:S)],'Sheet1'[Position 1]-1))
    MM = IF('Sheet1'[Position 1]=0||'Sheet1'[Position 2]=0,"0",
MID('Sheet1'[Resolution Time (H:M:S)],'Sheet1'[Position 1]+1,'Sheet1'[Position 2]-('Sheet1'[Position 1]+1)))
    SS = RIGHT('Sheet1'[Resolution Time (H:M:S)],LEN('Sheet1'[Resolution Time (H:M:S)])-'Sheet1'[Position 2])

     

     

    Finally,create a measure to get the value of average:

     

     

     measure = AVERAGE(Sheet1[Minutes])

     

     

    For the related .pbix file, pls click here.

     

    Best Regards,

    Kelly

     

     

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi Anonymous ,

 

As what have said by amitchandak 

 

To convert HH:MM:SS(which is a text format) to Minutes format,  you need to create  a calculated column, which is as below:

 

 

Minutes =
var a = LEFT('Table'[Date],LEN('Table'[Date])-6)
var b = MID('Table'[Date],LEN('Table'[Date])-(LEN('Table'[HH])+1),2)
var c = RIGHT('Table'[Date],2)
Return
a*60+b+c/60

 

To convert HH:MM:SS(which is a date format) to Minutes format, you need to create another calculated column, which is as below:

 

Minutes = 
var a =FORMAT('Table (2)'[Date2],"HH")
var b =FORMAT('Table (2)'[Date2],"mm")
var c=FORMAT('Table (2)'[Date2],"ss")
return
a*60+b+c/60

 

As for converting Minutes to HH:MM:SS, you can only convert Minutes to HH:MMif the minute is a whole number, or convert Minutes to HH:MM:SS format when the minute is a decimal number, such as 356.2 Minutes to HH:MM:SS,you need to create the following calculated columns:

1.convert Minutes which is  a whole number to HH:MM,

 

Get minute = MOD('Table (2)'[Minutes],60)
Get hour = DIVIDE('Table (2)'[Minutes],60)

 

2.convert Minutes which is  a decimal number to HH:MM:SS,

 

ss = ('Table (3)'[Minutes 2]-ROUND('Table (3)'[Minutes 2],0))*60
MM = mod(ROUND('Table (3)'[Minutes 2],0),60)
HH = DIVIDE('Table (3)'[Minutes 2],60)

 

For the related .pbix file,you can turn to the URL:

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/ES2gD4Ns3PFBqoAx3s_z5rsByfWWPbE8qxwU6ok8YpLWPQ?e=9v9xCx

 

Hope this would help.

 

Best Regards,

Kelly

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Kelly,

     

    Trying the same but getting this error pls help, when trying LEN & Left independently its working

     

     

    Regards.

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi Anonymous ，

       

      Left function is used for a text type data, be sure that the column of “Response time (H:M:S)” is a text type data, not a  date type.

       

      If the column Response time (H:M:S) is a date type, please create a calculated column below:

       

      Minutes =
var a =FORMAT('Table (2)'[Date2],"HH")
var b =FORMAT('Table (2)'[Date2],"mm")
var c=FORMAT('Table (2)'[Date2],"ss")
return
a*60+b+c/60

       

       

      Thanks.

       

      Best Regards,

      Kelly

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Kelly,

         

        Thanks for your time. ideally, i wish to calcualte the Avearge in HH:MM;SS format.

         

        Ist possible?