web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Apps Return Closest Value

Power Apps

Answered

Return Closest Value

(0) Share

Report

Report

Posted on by FosterM

12

Hi all,

I have a SQL Table that has 2 columns, 'Lookup' and 'Result'.

The app requires 3 Inputs from the user and returns a decimal result.

I'm looking for way to search the SQL table and return the closest 'Result' where Value is <= the result of the calculation.

I have tried the Lookup formula:

LookUp(Ullage,Lookup<=Value(luCalc.Text),Result)

However this returns only the first value on the table.

I also thought about reducing the dataset and taking the last() value but I cant find a way to reduce it based on comparing numbers.

Can anyone offer any advice?

Categories:

Building Power Apps

I have the same question (0)

All responses Img

All responses (2)

Answers Img

Answers (1)

Verified answer

LaurensM 12,516 Moderator on at

Like (1)

Report
Hi @FosterM,

Does the following code meet your requirements?
Filter the Table and sort by descending order, then use First() to fetch the closest value to that number:

//Only show the first (closest) record First( //Sort by closest value first Sort( //Fetch all rows that meet the requirement Filter( Ullage, Lookup <= Value(luCalc.Text) ), Result, Descending ) ) //You could write .Result after the last bracket to only fetch the Result column

I based my code and column names on your example - adjust where necessary. (I don't know if the lookup <= Value() was meant to be Value <= Value())

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!

Was this reply helpful? Yes No
FosterM 12 on at

Like (0)

Report

Thank you @LaurensM,

This worked perfectly as written. Interestingly I had to include the .Result - otherwise the filter() complained about expecting a text value.

You made the right assumption with the field name, thanks!

Was this reply helpful? Yes No

Join the conversation