topic Re: Split time value column in DAX in DAX Commands and Tips

Split time value column in DAX

chavanr — Tue, 26 Jan 2021 21:51:48 GMT

Hi there,

I have created a new calculated column with "concatenate" function & need to split the same new column as per the "-" seperator.

I need the time value which is on the right side as my Output, for which I have used RIGHT function but it is not quite useful as the space length is giving a extra letter from the concatenate function for some values if I adjust the length then I miss the one letter (please see below)

I was wondering if we anyone has solution to fix this issue.

Many thanks !!

Re: Split time value column in DAX

amitchandak — Wed, 27 Jan 2021 02:28:07 GMT

chavanr , Try a new column like this in DAX

right([time in new] , len([time in new]) - search("-",[time in new],,0)+1)

Re: Split time value column in DAX

chavanr — Wed, 27 Jan 2021 02:22:51 GMT

Thanks amitchandak will try !!

Re: Split time value column in DAX

chavanr — Thu, 28 Jan 2021 01:34:25 GMT

Hi amitchandak

Facing a issue with format now, after concatenate, the "Time In New" column is converted to text.

Error message"Cannot convert value '9:32:30 AM-' of type text to type number.

I did use the FORMAT for the "Time In New" column but is still gives in text format

Is there a workaround for this one?

Many thanks!!

Re: Split time value column in DAX

AnkitKukreja — Thu, 28 Jan 2021 12:34:34 GMT

Hi chavanr

I hope I got your reuirement right, please try below dax

Time in New2 =

RIGHT( 'Time Split'[TIme in new] , 10)

If still it doesn't work create another column and use Trim function to remove extra spaces.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Re: Split time value column in DAX

chavanr — Wed, 03 Feb 2021 03:33:37 GMT

Hi AnkitKukreja

I am already using the RIGHT function & also tried to use the trim function(to remove spaces) as well but not useful as it is converting the data type 'time' to 'text' & removes the seconds value for the time stamp

Re: Split time value column in DAX

Anonymous — Wed, 03 Feb 2021 06:02:40 GMT

Hello chavanr
You can try this.

New Time = RIGHT( Recordings[Time], 11)
After calculating change to time.