cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
colbyturybury
Helper III
Helper III

Filter a collection for a single result

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)

 

colbyturybury_0-1627060610630.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

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)

View solution in original post

8 REPLIES 8
BCLS776
Community Champion
Community Champion

Try a Lookup() instead:

 

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

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)

View solution in original post

colbyturybury
Helper III
Helper III

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

 

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

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
Helper III
Helper III

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

 


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

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?

 

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,990)