topic Re: Streaming Dataset - Latest value for each category in Developer

Streaming Dataset - Latest value for each category

shb — Sun, 24 Sep 2017 15:27:20 GMT

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

Re: Streaming Dataset - Latest value for each category

Eric_Zhang — Mon, 25 Sep 2017 07:24:14 GMT

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

Re: Streaming Dataset - Latest value for each category

shb — Mon, 25 Sep 2017 12:35:49 GMT

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

Re: Streaming Dataset - Latest value for each category

shb — Mon, 25 Sep 2017 13:07:14 GMT

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.

Re: Streaming Dataset - Latest value for each category

Anonymous — Mon, 26 Mar 2018 18:22:56 GMT

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

Re: Streaming Dataset - Latest value for each category

Anonymous — Fri, 21 Jun 2019 18:28:36 GMT

what was the formatting issue?

Re: Streaming Dataset - Latest value for each category

Anonymous — Mon, 24 Jun 2019 21:36:48 GMT

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.