Forum Discussion
Issue with measure return value - VALUE() - Cannot convert value '1.INF' of type text to type number
I am working with a few different measures that are used to standardise a set of values across multiple inputs.
Now I have hit an issue where the PERCENTILEX.INC calculation is bringing back NaN for the measure: MdxScript(Model) (953, 13) Calculation error in measure 'Fact....'[LowerMiddle]: Cannot convert value '-1.#IND' of type Text to type Number. I also got back a -1#INF cannot convert as well. The values used to create the measure that is being iterated is the created by adding then dividing two very small numbers. a table of examples is below.
| 1.8018E-05 | 6.30631E-05 |
| 0.015151515 | 0.068484848 |
| 0 | 0.0260625 |
| 5.71429E-05 | 0.0001 |
| 0.004833333 | 0.015166667 |
| 0.010666667 | 0.050666667 |
| 0.004625 | 0.011875 |
The file displayed a popup saying some data in the file was corrupt on first open.
As the error message said, the involved column in your calculation is text type. You must convert the values like "5.71429E-05" into numbers then change the data type for both columns into decimal number.
Regards,
After reviewing the measures further, there was another measure being used further down in the chain of calculations that was still of Format General. Changing this to decimal solved the issue. I am not sure why all the other calculations could work with this scientific format without the conversion and PercentileX.Inc could not (as it appears).
Now Resolved.
3 Replies
As the error message said, the involved column in your calculation is text type. You must convert the values like "5.71429E-05" into numbers then change the data type for both columns into decimal number.
Regards,
Thanks, I had tried this already, using the modeling tab, in the formatting group, I changed the Format to Decimal number on all of the measures and fields related to the calculations I am using. It turns out that another measure in the Lower Middle measure was where the error was pointing. I navigated to the measure and changed the Formats of the Measure and the Measures in the divide calculation as well as surrounded them with a Value function call. This calculation still works as expected... When using it in the Percentile Function I still get the Text to Number error message even though all my Formats are in Decimal.
After reviewing the measures further, there was another measure being used further down in the chain of calculations that was still of Format General. Changing this to decimal solved the issue. I am not sure why all the other calculations could work with this scientific format without the conversion and PercentileX.Inc could not (as it appears).
Now Resolved.