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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Kelly,

 

Please helpp on this.

 

Please find the attched source and PbIX and help me to find the average time.

 

REgards,

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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