Last sensors readings in Power BI

If you want to show all sensors and only their last reading value on a Power Bi dashboard then you're reading the right page.
It is not as trivial as it looks to be. So lets imagine we have a streaming dataset with our IoT sensor data. The data structure is as follows by this example data coming from IoT sensors
{ "Id": "sensor1", "Val": 11, "DateTime": "2020-05-06 10:11:05.222}
{ "Id": "sensor2", "Val": 5,   "DateTime": "2020-05-06 10:11:05.111}
{ "Id": "sensor1", "Val": 10.5, "DateTime": "2020-05-06 10:01:05.333}
{ "Id": "sensor2", "Val": 4.5,   "DateTime": "2020-05-06 10:01:06.334}
{ "Id": "sensor1", "Val": 10, "DateTime": "2020-05-05 10:11:05.122}
{ "Id": "sensor2", "Val": 4,   "DateTime": "2020-05-05 10:11:05.343}
{ "Id": "sensor1", "Val": 9,   "DateTime": "2020-05-04 10:11:05.566}
{ "Id": "sensor2", "Val": 3,   "DateTime": "2020-05-04 10:11:05.564}

On the Power Bi dashboard we would like to see a list of all sensors we have and the current value.
sensor1, 11
sensor2, 5

Naturally we do not know how many books we have so we just cannot use simple LIMIT/TOP functions. Out of the box PowerBI is not able to show this.

However we are able to pin Power Bi Report to the Dashboard. So we could create a report presenting the data and that would work fine. Unfortunately we are using the streaming dataset so the capabilities for reporting are limited.

We could achieve that with a Pyton or R scripts but that works too slow for presenting it on the Dashboard. Another solution is to use DAX expressions. You may create a New measure available in the Modeling tab in the Power Bi Desktop.

Firstly we will calculate the last DateTime for each sensor. So that would be the following expression

LastDateTime= VAR CurrentId = SELECTEDVALUE(RealTimeData[Id])

RETURN MAXX(FILTER(ALL(RealTimeData), RealTimeData[Id] = CurrentId), RealTimeData[DateTime])

And then the final expression that will show the value filtered by the DateTime

Last Val =

VAR CurrentLastDateTime = [LastDateTime]

RETURN MAXX(FILTER(ALL(RealTimeData), RealTimeData[DateTime] = CurrentLastDateTime), RealTimeData[Val])

Of course we should also filter by the Id, because if our DateTime is not unique then it will return wrong results. So the final expression would be

Last Val=

VAR CurrentLastDateTime = [LastDatetime]

VAR CurrentId = SELECTEDVALUE(RealTimeData[Id])

RETURN MAXX(FILTER(ALL(RealTimeData), RealTimeData[DateTime] = CurrentLastDateTime && RealTimeData[Id]=CurrentId ), RealTimeData[Val])

Once we add the table visualization to the report with Id and Last Val then we have what we wanted.

Now you just need to publish the report, and pin the part of the report to the dashboard.

And now the values refresh very well even if the data are coming frequently every second.

References:
https://youtu.be/HBFc1eBSfb8
https://whitepages.unlimitedviz.com/2019/05/creating-complex-real-time-dashboards-with-power-bi/amp/