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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        And your DAx is not identical to my dax.

         

        Please cross check it again.

        Your IF part should compare your condition with 0 not with LEFT(......).

        NOTE: FIND( ) and substitute are case Sensitive.