Showing results for 
Search instead for 
Did you mean: 
Advocate II
Advocate II

LookUp and Filter - Inconsistencies with using numbers for logical tests

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


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  

        '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...



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.
        'Average Temperature' = (-0.80) + 1.0  //-0.8 + 1 = 0.2
//This returns the expected result
     '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...

Advocate II
Advocate II

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.

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.



Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey @v-xida-msft,

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

Helpful resources

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,546)