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

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,109)