Streaming Dataset - Latest value for each category

[shb]

Hi,

I have a streaming dataset (e.g. real time temperature data) in Power BI via Stream Analytics. I have a category field called RoomName in that dataset (e.g. kitchen, bedroom) which allows me to see real-time temperature for each room. I want to see the latest temperature value for each room on a real-time basis in the following format:

Room	Temperature	Time
Kitchen	25	20/09/2017 17:12:30
Bedroom	23	20/09/2017 17:12:29
Lounge	24	20/09/2017 17:12:28

Could you please help how can I build the above data from the dataset? I have tried TOP 1 filtering based on the temperature value by latest time but that only gives me latest temperature value which in the above case is Kitchen temperature. I don't know how can I break that down by category. Any help will be appreciated. Thanks

[Eric_Zhang · Accepted solution]

---

@shb wrote:

Hi,

I have a streaming dataset (e.g. real time temperature data) in Power BI via Stream Analytics. I have a category field called RoomName in that dataset (e.g. kitchen, bedroom) which allows me to see real-time temperature for each room. I want to see the latest temperature value for each room on a real-time basis in the following format:

Room	Temperature	Time
Kitchen	25	20/09/2017 17:12:30
Bedroom	23	20/09/2017 17:12:29
Lounge	24	20/09/2017 17:12:28

Could you please help how can I build the above data from the dataset? I have tried TOP 1 filtering based on the temperature value by latest time but that only gives me latest temperature value which in the above case is Kitchen temperature. I don't know how can I break that down by category. Any help will be appreciated. Thanks

---

@shb

So you're trying to create a report in Power BI desktop connecting to a streaminddataset? If so, create two measures instead of using the Temperature and time column.

latest Time = MAX(RealTimeData[Time])

Latest Temperature = MAXX(FILTER(RealTimeData,RealTimeData[Time]=[latest Time]),RealTimeData[Temperature])

[shb]

Hi @Eric_Zhang, thanks a lot for your reply, looks like that may solve the problem however the following condition does not seem to be working for me as it is returning all the data instead of just the latest data. If I replace [latest time] with the static date value, the filtering does seem to work though. I have tried converting the [latest time] to date format but that didn't help either. Is there anything else I can try? Thanks

FILTER(RealTimeData,RealTimeData[Time]=[latest Time])

[shb]

Apologies, I believe it's due to the formatting of the datetime values that is different. I need the new measure showing date in MM/dd/yy hh:mm:ss tt format while the date in the dataset is dd/MM/yy hh:mm:ss tt. I tried to use Format function on [Latest Time] measure [Latest Time] =  Format (MAX([Latest Time]), "MM/dd/yy hh:mm:ss tt") but that is not working as I get error.

[Anonymous]

Hi,

Did you ever resolve this issue? I think I figured out the date format part but it always shows the first entry, not the last...

[Anonymous]

what was the formatting issue?

[Anonymous]

Anyone still stuggling with this dont stream millseconds in your jason file power bi seems to have an issue with filtering dates with this in.