Filter Dashboard Indicator to show most recent inspection?

[Laura]

I have a dashboard displaying data from fire hydrant inspections, with a relationship between the hydrant layer and the inspection table. Each hydrant can have multiple inspections over time. I want the indicators in the dashboard (e.g., number of leaking hydrants) to reflect only the most recent inspection for each hydrant.

For example, if Hydrant 102 was marked as leaking in an earlier inspection but is later marked as not leaking in a more recent one, I want the count of leaking hydrants to decrease accordingly (e.g., from 3 to 2).

I already have a filter set up to show only inspections where "Leaking = Yes", but this filter includes all inspections, not just the latest ones. I assume I can use a data expression as the source rather than the layer, but I am still a newbie and rely a lot on AI for that. 

[DavidPike]

It depends on your layer source.  If it's from an Enterprise Geodatabase you could create  SQL view and publish that.  If it's AGOL, you might need some workarounds.  

A Layer View on the record table probably won't work as the SQL is too limited.  But... An option might be to create a Joined View Layer How To: Create a Joined Hosted Feature Layer View in ArcGIS Online

You could join your Hydrant and Inspection layers (I guess Hydrant_ID is the join field).
Join One to One and Keep only first matching record (remember to sort by OBJECTID DESCENDING).  Join Type = Inner

[Laura]

Data is coming from SQL and being referenced in Portal (11.2) If I joined one to one would it automatically adjust the join when a new inspection is created? 

I don't know how else to go about sharing the information on problems for the latest round of inspections with live data when it is fixed.

[DavidPike · Accepted solution]

It's a join using a SQl view - so will always up-to-date (not like a static join in Pro).

It's worth a try as possibly the simplest option.  Just then set it as an additional source in your dashboard to filter via.

 Select & display most recent record  (page 2) also details an example SQL view with similar intended functionality (which you'd then publish to Enterprise).

[Laura]

I'll give a try next week, thanks!

[Neal_t_k]

 I am not seeing a max or last function in the filters. If you decide to go the expression route,  Maybe this can get you started:  https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-latest-sample-date-...

looks like a very similar question and solution.