Forum Discussion
Streaming Dataset - Latest value for each category
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 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
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])
6 Replies
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
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])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])
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.
