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

     

     

amitchandak's avatar
amitchandak
Super User
6 years ago

if it market as datetime or time

min = format(col1,"HH")*60 +format(col1,"MI")+ +format(col1,"SS")/60

 

If it is a text

Min = left(col,2)*60 + mid(col,3,2) + right(col,2)/60

 

In case there more than 99 hours , use search function to find the position of :

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601