Forum Discussion
Turning Format Text to number
Presumably some data is formatted like “00:4:21” or “4:31:12”. Since the code is expecting two numbers between each colon, it’s reading the minute number as “4:” which DAX can’t convert as a number.
You can either address this in your data, which might be more difficult, or you could replace your calculation with this:
duration in minutes = var textSize = LEN([Check in duration avg]) var l = SEARCH(":",[Check in duration avg]) var r = SEARCH(":",RIGHT([Check in duration avg],textSize - l)) return INT(LEFT(RIGHT([Check in duration avg],textSize - l),r - 1))
which should be a little more fault tolerant.
Presumably some data is formatted like “00:4:21” or “4:31:12”. Since the code is expecting two numbers between each colon, it’s reading the minute number as “4:” which DAX can’t convert as a number.
You can either address this in your data, which might be more difficult, or you could replace your calculation with this:
duration in minutes =
var textSize = LEN([Check in duration avg])
var l = SEARCH(":",[Check in duration avg])
var r = SEARCH(":",RIGHT([Check in duration avg],textSize - l))
return INT(LEFT(RIGHT([Check in duration avg],textSize - l),r - 1))
which should be a little more fault tolerant.
Thank you!!