Real Time Stream Problems in Power Bi Online

[maydaymiao]

I am doing an IoT project from industria that publishing data from OPC Server which collect machine data from PLCs to Azure event hub, and I use stream analytics to output the data to Power BI. And below is the query that I set in stream analytics.

SELECT
  co,
  id,
  avg(v) as v,
  System.Timestamp as Time
INTO
  [bioutput]
FROM
  [input]
GROUP BY
  TUMBLINGWINDOW(second,5),
  co,
  id

I have tried to create some dashboard in Power Bi Online these days and have three questons now. I see that there are other people also raised similar question, but most of solution are based on Desktop version? Anyways, appreicate that anyone can answer again in my scenario.

1) I am not able to use gauge widget to show a real time or "don't summarize" value like a real time temperature, it is either summarized, avg, min or max... Just want to confirm again whether it is really impossible. Meanwhile, I saw in the bottom of this link "https://powerbi.microsoft.com/en-us/documentation/powerbi-service-real-time-streaming/", looks like he uses line chart and card widget to show the real time stream. For line chart, yes, I am able to display live stream as well (I guess it is because of having timestamp in Axis?). But for card widget, again only having aggregation option instead of "don't summarize". Does anyone know how that guy achieve?

2) When I publish the report to dashboard, I found the page will not auto-refresh until manually click the refresh button which is not I expected.

3) As I use "System.Timestamp as Time" in the query, it is showing UTC time. How can I convert to local time? I am in China so should be UTC +8.0

[v-haibl-msft · Accepted solution]

@maydaymiao

For the first question, did you following the steps in this blog? We should be able to create a card that displays the most recently received value. There are detailed steps in it.

Both gauge and card visual need a aggregation value. To show the current temperature, you can also create a measure like following formula and put this measure into visual value. But you need to use Power BI Desktop to create the measure.

CurrentTemperature =
CALCULATE (
SUM ( Table1[Temperature] ),
FILTER ( Table1, Table1[DateTime] = MAX ( Table1[DateTime] ) )
)

For the second question, did you visualize the tiles with streaming tiles? Please take a look at the “Putting it all together: visualizing the data in Power BI” section in this blog.

Best Regards,

Herbert

[maydaymiao]

Thanks Herbert. I have not tried Power BI Desktop yet, definitely I will look into it later.

Meanwhile, I just checked that blog. The good news is I simulate the same in my Power BI Online successfully. However, the bad news is, it does not fit to my scenario.

Looks like his data format is： 

[ {
“timestamp” : “2016-09-10T01:26:45.030Z”,
“temperature” : 98.6,
“humidity” : 98.6
} ]

When he uses "Custom Streaming Data" in the tile, he will see either temperature or humiditiy as value field, once he selcted, it will display the real time stream. That make sense.

In my scenario, my data format is (cannot change):

[{

"co" :"AAAAA555555",

"id" :"AAAAA555555",

"v" :98.6,

"chinatime" :"2016-11-18T04:39:06.439Z",

"pasuruantime" :"2016-11-18T04:39:06.439Z",

"gmttime" :"2016-11-18T04:39:06.439Z"

}]

For example, if it is a temperature sensor, then the id and value field will be {"id":"temperature", "v": 25.6}; if it is a humidity sensor, then the field will be {"id":"humidity", "v": 56.8}. The problem is when I use streaming tiles, I can only select "v" as value field, but there is no option to have a filter function so that I can tell Power BI the v is for temperature or humidity.

[v-haibl-msft]

@maydaymiao

Do you mean that in your streaming dataset, “v” can either be temperature or humidity? Is it possible to create two streaming datasets, one for humidity sensor data and one for temperature sensor data?

Best Regards,

Herbert

[maydaymiao]

I have solved the UTC time convert issue by using DATEADD(hour,8,System.Timestamp) as ChinaTime

Appreciate that anyone can answer other two questions?