Skip to main content

Notifications

Power Apps - Microsoft Dataverse
Unanswered

Validation Error for Calculated Column in Dataverse – Exceeding Value Range

(0) ShareShare
ReportReport
Posted on by

I'm facing a validation error in Dataverse when trying to store values in a calculated column named 'Margem'. The error indicates that the value is outside the valid range. Here's the error message I received:

 

FarmaVIP_0-1710108177353.png

 

 

The 'Margem' column is calculated with the following formula:

 

Margem = [Valor_Venda_S_IVA] - [Preco_Custo_S_IVA_produto]

To prevent values from exceeding the maximum allowed range, I've attempted to introduce an if statement to limit the output of the formula. Despite this, the error suggests that the calculated value is reaching 2.42E+17, which is significantly higher than the maximum permitted value of 1,000,000,000.

 

FarmaVIP_5-1710107476080.png

 

Additionally, I've sorted the 'Margem' values in both ascending and descending order to identify any abnormal values that might correspond to the error message's value size, but this has not clarified the issue.

This seems to be a straightforward calculation, yet I am encountering this error on four different columns that perform similar calculations. I'm at a loss as to why this is happening.

Has anyone encountered this issue or can anyone advise on a solution? Any insights or suggestions to resolve this validation error would be greatly appreciated.

 

 

FarmaVIP_4-1710107401681.png

 

  • Drew Poggemann Profile Picture
    Drew Poggemann 9,273 on at
    Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

    Hi @FarmaVIP ,

    The issue looks to be with your import process in Power Query if the number is coming in without the decimal separation. The setting with the comma should be fine if you have your environment set with that configuration.  I would focus on your dataflow (if that is what you are using) and see if it is an error in the logic.

     

    Also, your calculated field is setup with 0 as a lower bounds, based on your first error screen, you will want to set this to a negative value since you have negative values coming in from your source.

  • Jonathan Manrique Profile Picture
    Jonathan Manrique 2,678 on at
    Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

    Hi @FarmaVIP 

     

    As you mention, it depends on the regional configuration and must be set by default to the creation language of the environment, so if the environment the language is Portuguese and the regional configuration is also Portuguese, it should have that behavior.

    However, each user can change that configuration, you must validate that the users are using the corresponding configuration.

    It is more an issue of data manipulation than of the system, if they are placing , or . It makes a big difference. I am in Spain and we use a comma and I have never had that problem

     

     

  • FarmaVIP Profile Picture
    FarmaVIP on at
    Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

    I've tried the approaches suggested by@dpoggemann @Jmanriquerios 

    After checking the original columns and checked the values in the dataverse table, I confirmed there are no nulls/errors or excessively large values. Following some tests, I believe the issue may be related to the commas.

     

     

    Here's an example demonstrating the problem in Power Query.

    FarmaVIP_1-1710192255166.png

    The error number in my Power Query is twenty-one thousand one hundred fifty-two point fifty-two (21152,52 ). However, it becomes twenty-one million one hundred fifty-two thousand five hundred fifty-two ( 2115252) when it enters the Dataverse.

    FarmaVIP_2-1710192356409.png

     

    I am from Portugal and my setting for my environment are these.

    In Portugal, we use commas as the decimal separator in numbers.

    FarmaVIP_3-1710192633015.png

    Any way to fix this? 

     

     

     

     

  • Drew Poggemann Profile Picture
    Drew Poggemann 9,273 on at
    Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

    HI @FarmaVIP ,

    Have you looked at the values for the Valor_Venda_S_IVA and Preco_Custo_S_IVA_produto in Dataverse?  I see you are show the values in the image above from it looks like Excel.  Wondering if you could show the values in Dataverse that are causing this failure?  I am expecting it has to do with the data import process where a field is getting set to a value you are not expecting...  

  • Jonathan Manrique Profile Picture
    Jonathan Manrique 2,678 on at
    Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

    Hi @FarmaVIP 

     

    Hello, maybe you could change the approach you are using and in this way validate the same but on the side of the columns that you use for the calculation. When you create the columns you can limit the minimum and maximum value, something that you cannot do in the calculated ones.

     

    So what I would do is limit the values in the columns that I need for the operation and this way when I do the operation through a calculated column I will no longer need to validate because I did it previously

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard

Featured topics