Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DanielClark
Helper II
Helper II

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-056.30631E-05
0.0151515150.068484848
00.0260625
5.71429E-050.0001
0.0048333330.015166667
0.0106666670.050666667
0.0046250.011875

 

The file displayed a popup saying some data in the file was corrupt on first open.

Daniel Clark
Business Insight Data Modeler @ BRIGHTSTARR
2 ACCEPTED SOLUTIONS
v-sihou-msft
Employee
Employee

@DanielClark

 

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,

View solution in original post

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.

Daniel Clark
Business Insight Data Modeler @ BRIGHTSTARR

View solution in original post

3 REPLIES 3
v-sihou-msft
Employee
Employee

@DanielClark

 

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.

Daniel Clark
Business Insight Data Modeler @ BRIGHTSTARR

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.

Text to Number from Z-Score.PNG

Daniel Clark
Business Insight Data Modeler @ BRIGHTSTARR

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.