cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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:

 

RoomTemperatureTime
Kitchen2520/09/2017 17:12:30
Bedroom2320/09/2017 17:12:29
Lounge2420/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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft


@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])

Capture.PNG

View solution in original post

6 REPLIES 6
Highlighted
Microsoft
Microsoft


@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])

Capture.PNG

View solution in original post

Highlighted

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])

 

Highlighted
Frequent Visitor

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.

Highlighted
Regular Visitor

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...

Highlighted

what was the formatting issue?

Highlighted

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Kudoed Authors