Filter a collection for a single result

[colbyturybury]

I have a collection that has two columns. One column is called CompressiveStrength and the other is called Strain. There is a formula that returns one of the numbers in Compressive Strength and I need the Strain value to appear in a label. Below is an image. Basically search for a number in the left column and return the value n the right column.  I tried both of these formulas and neither work.

Search(colUnconfinedStrength, lblUnconfinedStrength.Text, "Strain")

Filter(colUnconfinedStrength, Strain = lblUnconfinedStrength.Text)

[BCLS776]

Try a Lookup() instead:

Lookup(colUnconfinedStrength, Strain = lblUnconfinedStrength.Text, CompressiveStrength)

[colbyturybury · Accepted solution]

Very close. You got me on the right path though! I had to reverse what column you were looking into and make sure the value in the text box was a number.

LookUp(colUnconfinedStrength, CompressiveStress = Value(lblUnconfinedStrength.Text),Strain)

[colbyturybury]

So this was working and for some reason I now receive a blank value.

LookUp(colUnconfinedStrength, CompressiveStress = Value(lblUnconfinedStrength.Text),Strain)

[BCLS776]

The CompressiveStress lookup column in your collection has values shown to 7 decimal places of precision, and a rounding difference could make an expression with '=' not work properly. Is there a better way to set this up?

[colbyturybury]

What if I rounded the data before it entered the CompressiveStress column in the collection?

[BCLS776]

---

@colbyturybury wrote:

What if I rounded the data before it entered the CompressiveStress column in the collection?

---

That could help, but what I was getting at was that the expression itself could be re-worked to ensure it captures values that are "nearly right". For example,

LookUp(colUnconfinedStrength, 
    CompressiveStress > (Value(lblUnconfinedStrength.Text)-1) &&
    CompressiveStress < (Value(lblUnconfinedStrength.Text)+1) ,
    Strain
)

This expression would return values of Strain that correspond to a +/- 1 unit interval around the given CompressiveStress. I'm not sure if this is an acceptable way to handle the calculation in your app, but it is an option.

[colbyturybury]

A simple pasting of your formula did work. I will need to verify this through a number of tests. Does power apps struggle with high number of decimal point values?

[BCLS776]

Power Apps doesn't necessarily struggle with decimal places but like any digital representation of numbers, things can get a bit weird at the limits and equivalence is a fleeting concept (e.g. 3.14159265 <> 3.1415927 even if you show it to 6 decimal places). Check out this doc for some more info.