Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert Text Data type to whole number

(1) Hi Power BI folks, can you assist me in how to convert this text dataype which written in terms of day,hour,minute and second       to  seconds because I cannot  use the AVERAGE(V_SLA[Business e...
dataype
  • Anonymous's avatar
    Anonymous
    6 years ago
    Hi Anonymous ,
    I have create below column for Hour MIN SEC requirement.
    PLease modify it little bit as you have days as well. Incase you are unable to do let me know i will do.
     
    REq_Time_Format = IF(SEARCH("sec",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]),"SEC",""),IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]) & "00")
     
    then go to modelling tab and convert this column to time format and type: HH:MM:SS:TT
     
    then create one more column or you can add calculation in previous formula itself.
     
    In_Seconds = 'Table'[REq_Time_Format]*86400
     
    then perform your calculations &
     REq_Time_Format is already in your required format so you can display it as it is.
     
    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.
     
  • Anonymous's avatar
    Anonymous
    6 years ago

    AnonymousHi, I am getting the following error: DAX comparison operations do not support comparing values of type integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Hi Anonymous ,
I have create below column for Hour MIN SEC requirement.
PLease modify it little bit as you have days as well. Incase you are unable to do let me know i will do.
 
REq_Time_Format = IF(SEARCH("sec",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]),"SEC",""),IF(SEARCH("min",'Table'[Time],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Time],1,0)>0,SUBSTITUTE('Table'[Time]," hour ",":"),"00:" & 'Table'[Time])," minute",":"),"00:00:" & 'Table'[Time]) & "00")
 
then go to modelling tab and convert this column to time format and type: HH:MM:SS:TT
 
then create one more column or you can add calculation in previous formula itself.
 
In_Seconds = 'Table'[REq_Time_Format]*86400
 
then perform your calculations &
 REq_Time_Format is already in your required format so you can display it as it is.
 
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    Create one [Days] Column =

    Days = IF(FIND("day",'Table'[Time],1,0)>0,LEFT('Table'[Time],FIND("day",'Table'[Time],1,0)-1),"0")
     
    Create another column for remaining part=
    Remaining_Part = IF(FIND("day",'Table'[Time],1,0)>0,MID('Table'[Time],FIND("day",'Table'[Time],1,0)+4,LEN('Table'[Time])-FIND("day",'Table'[Time],1,0)+4),'Table'[Time])
     
    Create time format column 
     
    Time Format = IF(SEARCH("sec",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE(IF(SEARCH("min",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE('Table'[Remaining_Part]," hour ",":"),"00:" & 'Table'[Remaining_Part])," minute",":"),"00:00:" & 'Table'[Remaining_Part]),"sec",""),IF(SEARCH("min",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE(IF(SEARCH("hour",'Table'[Remaining_Part],1,0)>0,SUBSTITUTE('Table'[Remaining_Part]," hour ",":"),"00:" & 'Table'[Remaining_Part])," minute",":"),"00:00:" & 'Table'[Remaining_Part]) & "00")
     
    After that go to modelling tab and change date type = Time and format =HH:MM:SS:TT
     
    and final Second Column/Measure=
    In_Seconds = 'Table'[Time Format]*86400+'Table'[Days]*86400
     
     
     
     
     
    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      AnonymousHi, I am getting the following error: DAX comparison operations do not support comparing values of type integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous 

         

        Pleae  check your DAX.

        In IF part part when condition is true you are returning interger value(Find() function returns integer position.)

        In else part you are returning "0" which is string. So instead of using "0" use 0.

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

        If I resolve your problem Mark it as a solution and give kudos.