topic Convert Text Data type to whole number in DAX Commands and Tips

Convert Text Data type to whole number

Anonymous — Wed, 08 Jan 2020 08:59:05 GMT

(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 elapsed time]) measure
(2) I need to convert it into whole number, find the average and then change the format back to original

(3) It returns error when I convert it to duration data type

Re: Convert Text Data type to whole number

Anonymous — Wed, 08 Jan 2020 11:55:37 GMT

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.

Re: Convert Text Data type to whole number

Anonymous — Wed, 08 Jan 2020 14:23:03 GMT

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.

Re: Convert Text Data type to whole number

Anonymous — Thu, 09 Jan 2020 01:22:39 GMT

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

Re: Convert Text Data type to whole number

Anonymous — Thu, 09 Jan 2020 08:15:49 GMT

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.

Re: Convert Text Data type to whole number

Anonymous — Thu, 09 Jan 2020 08:18:22 GMT

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.