Forum Discussion
Decimal Parameters in User Defined functions (UDF)
On their website, Microsoft defines decimal numbers as:
"Decimal number is the most common number type, and can handle numbers with fractional values and whole numbers. Decimal number represents 64-bit (eig.ht-byte) floating point numbers with negative values from -1.79E +308 through -2.23E -308, positive values from 2.23E -308 through 1.79E +308, and 0. Numbers like 34, 34.01, and 34.000367063 are valid decimal numbers."
However, when you author a UDF and define a decimal (val) parameter, the value gets truncted exactly after the 4th decimal digit.
For example if you pass a value of 0.0045 as a value to a UDF, with a parameter defined as DECIMAL VAL, and then inside the UDF you divide this parameter by 230, the outcome of this calculation will be exacly zero.
If you are dealing with smaller than 4 decimal places numbers or calculations, I reccommend using Double parameters
Hi ZvikaBK2801,
Thank you for reaching out to Microsoft Fabric Community.
Thank you avinash_7995 for the prompt response.
This is expected. In a DAX UDF, the decimal type is a fixed decimal (currency) type that only keeps 4 decimal places, so 0.0045 / 230 rounds to 0.
For calculations that require more than 4 decimal places, use the Double type instead, as it is designed for higher precision values.
Thanks and regards,
Anjan Kumar Chippa
Hi, yes the solution had been provided thanks. I'm now using Double for all my non integer UDF parameters. I also notified the couple PBI local user groups about this issue.
7 Replies
Thanks for sharing. However, I don't think this is an inherent limitation of the "Decimal" data type. A decimal value like "0.0045 / 230" equals approximately "0.0000195652", which should not become zero simply because it's stored as a decimal.
If the result is being truncated after four decimal places, it's more likely due to the UDF implementation, a fixed decimal scale (for example, "DECIMAL(...,4)"), or how the result is being displayed, rather than the "Decimal" type itself.
In general, "Decimal" is preferred for precise decimal calculations (such as financial data), while "Double" is better suited for scientific calculations where a larger range is needed and small floating-point rounding errors are acceptable.
Hi,
It's not an issue of how the UDF outcome is displayed, nor is it any delibarate or indeliberate trancation under user repsponsibility. Why don't you try it out for yourseft insteat of thinkg how PBI should work.
v-achippaCommunity Support
Hi ZvikaBK2801,
Thank you for reaching out to Microsoft Fabric Community.
Thank you avinash_7995 for the prompt response.
This is expected. In a DAX UDF, the decimal type is a fixed decimal (currency) type that only keeps 4 decimal places, so 0.0045 / 230 rounds to 0.
For calculations that require more than 4 decimal places, use the Double type instead, as it is designed for higher precision values.
Thanks and regards,
Anjan Kumar Chippa
powerbidev123Solution Sage
Hi ZvikaBK2801
Microsoft defines decimal numbers on its website as follows:
“Decimal number is the most common number type, and can handle numbers with fractional values and whole numbers. Decimal number represents 64-bit (eight-byte) floating-point numbers with negative values from -1.79E+308 through -2.23E-308, positive values from 2.23E-308 through 1.79E+308, and 0. Numbers like 34, 34.01, and 34.000367063 are valid decimal numbers.”However, when authoring a UDF and defining a Decimal (val) parameter, the value appears to be truncated after the fourth decimal place.
For example, if you pass 0.0045 into a UDF as a parameter defined as DECIMAL VAL, and then divide that parameter by 230 inside the UDF, the result is exactly zero.
If you are working with numbers smaller than four decimal places, or calculations that require greater precision, I recommend using Double parameters instead.
