Push Dataset Latest Date for Given Series of Rows

I have a push dataset that looks similar to this:

Site, Status, Latitude, Longitude, Service, Service Status, Status Date

Given that it is a push dataset, I have lots of rows; one for each Site/Service/Status Date combination in the history.

In DAX visualizations, I need to show only the lastest status rows for a given Site.  Think something like the following in SQL:

SELECT *

FROM Table t

INNER JOIN (

SELECT Site, MAX([Status Date]) AS Latest

FROM Table

GROUP BY Site) g

ON g.Site = t.Site

WHERE [Status Date] = Latest

Keep in mind with a push dataset, I cannot add new tables to the model (without manipulating tables and relationships through the REST API, which I'm trying to avoid and just use the rows endpoint witht the predefined API key).

I've tried using GROUPBY and storing the result in a table variable, then using a NATURALINNERJOIN, however the columns returned by GROUPBY cannot be referenced by any functions other than interators.  But if you use an interator function such as MAXX, the returned date is the same as the current row date (row context vs aggregate):

GroupByTest =
VAR LatestSiteStatusDates = GROUPBY(RealTimeData, RealTimeData[Site], "Most Recent Status", MAXX(CURRENTGROUP(), RealTimeData[Status Date]))
VAR LatestSiteStatusJoin = NATURALINNERJOIN(LatestSiteStatusDates, RealTimeData)
RETURN CALCULATE(MAXX(LatestSiteStatusJoin, [Most Recent Status]))

I've also tried LOOKUPVALUE, but as previously mentioned about any function that isn't an interator, the columns in the table variable are inaccessible to it.

Any ideas?

Thanks!

Kevan