LookUp and Filter - Inconsistencies with using numbers for logical tests

[AccuFloDev]

(Re-posting because my last post dissapeared after updating it)

Hello,

I have a Canvas app with form which needs to pull a range of records from an Embedded Excel file using either LookUp or Filter. This range of records is pulled using a number value from a text field in the form.

For example, if I have a value of -0.8 (representing an Average Temperature) that is entered into a Text field called DataCardValueNvAvgTemp, I want to pull a record from the Excel file that is +1.0 from that value (-0.8 + 1.0 = 0.2).  

Note that the column I am querying is set to a Number data type in excel, and I have confirmed the value of 0.2 exists in the file.

I am attempting to do this query dynamically with the following formula, which returns no records:

//This function returns no records
Filter(
   NetVcfat510,  //This is the excel file
   'Average Temperature' = Value(DataCardValueNvAvgTemp.Text) + 1.0  //DataCardValueNvAvgTemp value is -0.8
)

However, if I hard-code the value of 0.2, the query succeeds  

Filter(
        NetVcfat510,
        'Average Temperature' = 0.2
)

What is going on here?  Why does the first formula fail while the second succeeds?  Am I missing something here?
I have confirmed with a Label that 'Value(DataCardValueNvAvgTemp.Text) + 1.0' is 0.2, exactly the same as my hard-coded value...

EDIT:

I did some more testing on this using hard-coded numbers, it appears it may be related to how Numbers are stored in the back end:

//This returns no results.
Filter(
        NetVcfat510,
        'Average Temperature' = (-0.80) + 1.0  //-0.8 + 1 = 0.2
)

//This returns the expected result
Filter(
     NetVcfat510,
     'Average Temperature' = 0.2  //-0.8 + 1 = 0.2
)

2nd Edit:

More testing has revealed that it is indeed an error with how numbers are stored.  If I use the Round function to round to the nearest tenth of a decimal, the formula succeeds:

//This returns the expected result
Filter(
     NetVcfat510,
     'Average Temperature' = Round((-0.80) + 1.0, 1)  //This works
)

I'm not sure if this is expected behaviour but this is definitely not intuitive and will certainly cause non-coders a lot of headache trying to debug such inconsistencies...

[AccuFloDev]

I think I found the answer to my problem.
Firstly it looks like my issue was related to my data source: Common Data Service for Apps.

I wasn't expecting precision errors at one decimal place: I suspect it's because the floating point operation (-0.8 + 1.0 = 0.2 ) is false because of this floating point imprecision.   I will see if I get this behaviour with using the decimal data type.

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/types-of-fields#using-the-right...

[v-xida-msft]

Hi @AccuFloDev,

Have you solved your problem?

If you have solved your problem, please go head to click "Accept as Solution" to identify this thread has been solved.

[AccuFloDev]

Hey @v-xida-msft,

I'm still working on confirming it today.  I'll update this ticket when I have tested it.