Maximum Value for List by Group

(0) Share

Report

Posted on by RJF61

1,265

I have a SP List/Power App for vehicles. From below, the Vehicle No is the left column (i.e., 2, 9, 14, 21, etc.)

Currently, the "Items" properties is...

LookUp(VehInsp,
VehNo.Value = ThisItem.VehNo,
Text(Max(CurrentMileage),"#,#.0"))

For the gallery I would like to capture the max mileage value by Vehicle Number. Currently the Items is collecting the first record by Vehcile No.

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

Rajkumar_M 3,747 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @RJF61

In item gallery

ForAll(
Distinct(VehInsp, VehNo),
{
VehNo: Result,
MaxMileage: Max(Filter(VehInsp, VehNo = Result), CurrentMileage)
}
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.
RJF61 1,265 on at

Like
a
(0)

Report
Copy link

Link copied!
Hello @Rajkumar_404 Thanks for the response. So to ensure I am clear, the current Items for the gallery is...

Sort(
Filter(
VehList,
(Shop.Value=Dropdown2.Selected.Value ||
Dropdown2.Selected.Value = Blank()
) &&
(
VehMfg.Value = Radio1.Selected.Value ||
Radio1.Selected.Value = Blank()
)
),
VehNoN,
SortOrder.Ascending
)

The Items property I previously provided was specific to the Mileage column/field.

Do I incorporate what you provided into my existing gallery Items properties?...
ForAll(
Distinct(VehInsp, VehNo),
{
VehNo: Result,
MaxMileage: Max(Filter(VehInsp, VehNo = Result), CurrentMileage)
}
)
LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @RJF61,

Do I understand it correctly that you would like to see the Max mileage on each row for the corresponding VehNo? Instead of using a LookUp, which fetches the first record matching the condition, you could try the following in the Text property of that label:

//Style number accordingly Text( //Display the highest CurrentMileage Max( //Fetch records matching this VehNo Filter( VehInsp, VehNo.Value = ThisItem.VehNo ), CurrentMileage ), "#,#.0" )

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!
RJF61 1,265 on at

Like
a
(1)

Report
Copy link

Link copied!

@LaurensM Thanks for the response. With the suggested option, I am receiving the following...

Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The "Max" operation is not supported by this connector.

Suggestions?
LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@RJF61

A delegation warning is not an error but rather a notification that it may not work as intended when the list exceeds your Data Row Limit. Max is not a delegable function, causing the warning to be displayed. Since we use a delegable filter as the Max() input, this is nothing to worry about. An exception to this would be when the Filter would find > 2000 records, in other words when more than 2000 rows match the VehNo. (Power Apps can only return up to 2000 records from each query)

Is the function not working correctly in your app?

To hide the delegation warning you could use the With() function, but I would not recommend hiding warnings.

With( { wRecords: Filter( VehInsp, VehNo.Value = ThisItem.VehNo ) }, Text( Max( wRecords, CurrentMileage ), "#,#.0" ) )

I hope this helps!