Filter/Lookup Problem

(0) Share

Report

Posted on by abc3

207

Hi All,

I was using valuable to do the filter only few row cant be return

I was trying to do some testing

Lookup(table, a =vara && b=varb) it return null

Lookup(table, a =vara ,b) it can return b

then i set a if statement If(Lookup(table, a =vara ,b) = varb,1,0) it return 1

If( Lookup(table, a =vara && b=varb,b)= varb,1,0) return 0

Set(varb, Lookup(table, a =vara ,b)); Lookup(table, a =vara && b=varb) return null

Same result on the Filter function

Please help me fix this problem

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

BCBuizer 22,844 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @abc3 ,

Can you please share the values you used for vara and varb and the table? As you described it, indeed there seems to be an inconsistency, so I'd like to try to replicate the issue.

Was this reply helpful? Yes No
abc3 207 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @BCBuizer,
On vara = 50 varb=-120.83889
on the table col a= 50col b = -120.838890 save on dataverse Decimal field Decimal places was 6 I was try to replace b and varb to -120.8388 it work only not work on -120.83889 this number

Was this reply helpful? Yes No
BCBuizer 22,844 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @abc3 ,

The = operator does not return a true in case there isn't an exact match. Simply said: -120.8388 <> -120.83889

If you want to only compare for a certain number of decimals, use the RoundDown function:

Was this reply helpful? Yes No
abc3 207 on at

Like
a
(0)

Report
Copy link

Link copied!

@BCBuizer
sorry for my poor explanation
In my case is table value equal -120.838890 and varb-120.83889 it not work
but i try it to table value equal -120.838800 and varb -120.8388 it work
and -120.838880 value equal varb -120.8388 it work too
only when the table value and varb was -120.838890 equal to-120.83889 it will not work on it
more testing case
Table field varb
-120.838890 -120.83889 not result
-120.838800 -120.8388 have result
-120.838880 -120.83888 have result
-119.793945 -119.793945 not result
-103.336929 -103.336929 not result
-108.536118 -108.536118 not result
All of this have record on the dataverse
I have copy the table field value and hard code on the lookup field and still not return any result

Was this reply helpful? Yes No
abc3 207 on at

Like
a
(0)

Report
Copy link

Link copied!

@BCBuizer
i have create a new testing table only have one row
here you can see if i not doing any filtering it can show the record
when i filter it using name it is working well
but when i filter "a" the number i just copy above image
Sure this two value was equal

Was this reply helpful? Yes No
BCBuizer 22,844 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @abc3 ,

I was able to replicate the issue:

As a workaround I found that nesting the column name in a Value function makes it work, but that causes delegation issues:

This shouldn't be necessary since a decimal column also returns a number, so something is off.

@WarrenBelz , @Drrickryp, @iAm_ManCat : have you seen this before?

Was this reply helpful? Yes No
Verified answer

iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Yes, this is the way the columns are designed - you'll need to store it as a floating point type

The way decimal and floating point are stored is different, decimals are like pinpoint perfect storage to extreme number of decimal points whereas floating point is stored 'almost perfect' to nearest decimal point (and is hence much smaller in memory usage size) - There's an explanation here but I think this explanation below is much more applicable:

Think of it this way: in the app, you are saying 'is its value equal to -120.838890?'

But the app will not necessarily be using the decimal format when you are making it do this by typing out that number, in fact it will likely be using a float if you are typing a static number, so it might be doing:

-120.828890000000000000001 = -120.838890

which would be false

This is why you should be using floating point for something like this, as you want it to be approximately equal to the nearest decimal point, my example here showed this as well:

I configured Float to allow negative numbers by halving its default 1000000000 on either side of zero:

Then in the app, I can filter by it:

Cheers,

Sancho

Was this reply helpful? Yes No