Forum Discussion
Turning Format Text to number
I have formatted text values, but I want to switch them to number values so I can apply a conditional format based on if the time is between 0-30 mintes or 31+ minutes.
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.
4 Replies
Hello mgaut341
You can get the number of minutes from the formated string usingduration in minutes = INT(RIGHT(LEFT([Check in duration avg],5),2))As an aside, this board is dedicated to developing custom visuals, and thus a little less active than the other boards. You might be able to get your question answered by posting to another forum such as https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands.
Thanks for the feedback. I get the following error message
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!!
