Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shb
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
Eric_Zhang
Employee
Employee


@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
Eric_Zhang
Employee
Employee


@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

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

Anonymous
Not applicable

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
Not applicable

what was the formatting issue?

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors