How to send only the last value received from stream analytics to powerbi

Question

How can I send and keep only the last value sent from stream analytics to powerbi?

I am measuring temperatures per sensor and sending to an eventhub and then to stream analytics then I am sending that further to powerbi. But I donot want all values in a table in powerbi. I want just the last measured value to appear in powerbi.

The reason is that I want to show in a single tile just the last measured sensor value for one of the sensors. I was not able to find a way to that if I send all measurements from stream analytics to powerbi as there are no filters that let me select only the maximum of date time values for example.

How to do that?

Answer 1

We're looking at how to do this in a better way, but there's a pretty simple procedure for making this work:

Use the "Ask a question about the data on this dashboard" box (QnA) and use a question like the following: "Average speed in the last 10 seconds"

This will give you what you want.

HTH, -Lukasz

Signup: http://www.powerbi.com

Power BI Developer Blog: http://blogs.msdn.com/powerbidev

Developers: http://dev.powerbi.com

Support: http://support.powerbi.com

Answer 2

you can use MAX function to get latest timeseries value. For eg.

SELECT
    MAX(refTimeStamp) as time
INTO
    output
FROM
    input TimeStamp BY refTimeStamp
GROUP BY 
    TumblingWindow(minute, 3)

It will return latest time in last 3 minute window.

Answer 3

That can be achieved by using the

LAST

Analytical function that is built in.

as an example usage

SELECT
       sensorId, 
       LAST(reading) OVER (PARTITION BY sensorId LIMIT DURATION(hour, 1) WHEN reading IS NOT NULL)
FROM input 

the above sample query is going to find most recent non-null sensor reading over an hour duration.

click here for more info on LAST Function.

Answer 4

Another option is to use a slicer in the report and use it to slice datetime values. Also you can add filters to the reports to specify time.

However these will still be at the reports level, not the Dashboard.

Hope this helps.