cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AccuFloDev
Level: Powered On

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
AccuFloDev
Level: Powered On

Re: LookUp and Filter - Inconsistencies with using numbers for logical tests

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

Community Support Team
Community Support Team

Re: LookUp and Filter - Inconsistencies with using numbers for logical tests

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.
AccuFloDev
Level: Powered On

Re: LookUp and Filter - Inconsistencies with using numbers for logical tests

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,213)