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

LookUp and Filter - Inconsistencies with using numbers for logical tests

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

3 REPLIES 3
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.

 

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

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

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (11,289)