Answered

Only show latest instance of an item in a gallery

(0) Share

Report

Posted on by Will2

Hi, I'm pretty new to PowerApps, but have managed to get a gallery displaying in a canvas app to show items from a SharePoint online list, and to filter that down based on some other inputs. The list is displaying assessments for a set of risks, and I'd like to make it so that each risk only appears once for each location, showing the most recent assessment date and the assessment status on that date.

This is what I've currently got on the items property of the gallery

Filter('Compliance',Site.Value = SiteDropdown.SelectedText.Value && 'Risk:SectionText'.Value = RiskSectionDropdown.SelectedText.Value && (ComplianceLevel.Value in (CompList.SelectedItems.Value) Or (IsEmpty(CompList.SelectedItems.Value))))

The date field I want to use is called 'Assessment date' (and yes I regret that space in the field name)

So I'd like to add a condition to the filter that says something like "&& assessment date = [the latest assessment date for that Risk/Site]" - where both Risk and Site are lookup fields in the SharePoint list. Is this possible/is there another way to achieve the desired outcome?

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

MVP-Phipps 3,521 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

You can use the SortBy function to change the order for your gallery, please refer to this documentation: https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-sort

And use the Distinct function to only show one of each: https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-distinct

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.
LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
Subscribe to my YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Will2 25 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks but I'm not sure how to use the distinct function to do what I need here. I can add a sort to the start of the items property so it becomes

Sort(Filter('Compliance',Site.Value = SiteDropdown.SelectedText.Value && 'Risk:SectionText'.Value = RiskSectionDropdown.SelectedText.Value && (ComplianceLevel.Value in (CompList.SelectedItems.Value) Or (IsEmpty(CompList.SelectedItems.Value)))),'Assessment Date', Descending)

but how do I then add distinct to that to get only the most recent instance of each assessment, but including the details of the risk and site it relates to?
MVP-Phipps 3,521 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Rather than Distinct to get most recent add that logic within your filter. What determines if it is the most recent? Is there a column you can use to add to your filter?
Will2 25 on at

Like
a
(0)

Report
Copy link

Link copied!

There is a column of 'Assessment Date' which is what I need to base the most recent logic on - so for each risk at each site there may be multiple assessments, but I only want to show the most recent one in each case.
MVP-Phipps 3,521 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Then in your filter what you want to do is a lookup and take the greatest 'Assessment Date' per risk. Would you like help with this formula?
Will2 25 on at

Like
a
(0)

Report
Copy link

Link copied!

yes please! Note that it needs to be the greatest assessment date per risk at each site, rather than just per risk.
Verified answer

NickMerrick 419 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

https://powerusers.microsoft.com/t5/Building-Power-Apps/Show-Rows-in-a-Gallery-with-the-latest-record-on-Distinct-Values/td-p/854770
WarrenBelz 156,566 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
@Will2 ,

I cannot test this (so this is structure guidance only) and your use of the Lookup column has caused possibly unnecessary complexity (they are best avoided in Power Apps unless you have a reason for using them that cannot be done otherwise), but if you want all the fields back in only the latest Assessments dates per site

With( { wLast: AddColumns( GroupBy( AddColumns( Sort( Filter( 'Compliance', Site.Value = SiteDropdown.Selected.Value && 'Risk:SectionText'.Value = RiskSectionDropdown.Selected.Value && (ComplianceLevel.Value in CompList.SelectedItems.Value || Len(CompList.Selected.Value) = 0) ), 'Assessment Date', Descending ), "SiteVal", Site.Value ), "SiteVal" "Data" ), "AssessDate", First(Data).'Assessment Date", "IDRef", First(Data).ID ) }, Ungroup( ForAll( wLast As aLast, LookUp( 'Compliance', ID = aLast.IDRef ) ), "Value" ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
Will2 25 on at

Like
a
(1)

Report
Copy link

Link copied!

I managed to get a solution based on the structure in this link to work after a lot of fiddling. Thank you.