Forum Discussion
How to create summary table for a Push Dataset?
Greetings.
I have created a power bi streaming api push dataset, and I have a job keep sending data to push dataset api every minute. The schema is something like:
{
IncidentId: Text,
UpdatedDate:Date
State: Text
}
The sample data in push dataset is something like:
| IncidentId | UpdatedDate | State |
| 2 | 2020-09-25T00:03:00Z | Created |
| 1 | 2020-09-25T00:02:00Z | InProgress |
| 1 | 2020-09-25T00:01:00Z | Hold |
| 3 | 2020-09-25T00:01:00Z | Created |
| 2 | 2020-09-25T00:00:00Z | Created |
I want to filter/query this table to make sure I can get the latest state of each record, the logic will be: group by the incident id and get the record with latest updated date. and the result will be:
| IncidentId | UpdatedDate | State |
| 2 | 2020-09-25T00:03:00Z | Created |
| 1 | 2020-09-25T00:02:00Z | InProgress |
| 3 | 2020-09-25T00:01:00Z | Created |
I have tried filters visualization and also creating measures, they all are not supporing this scenario. Please let me know how I can reach this goal in powerbi?
Hi, copzyz123
It’s my pleasure to answer for you.
According to your description, I think you can make some modifications in the table format, and create measures to get the latest status and count the number of process.
Like this:
Measure:
latest status = LASTNONBLANKVALUE ( pushdateset[UpdatedDate], SELECTEDVALUE ( pushdateset[State] ) )Created = COALESCE ( COUNTROWS ( FILTER ( SUMMARIZE ( pushdateset, pushdateset[IncidentId] ), [latest status] = "Created" ) ), 0 )If you have other questions, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi, copzyz123
It’s my pleasure to answer for you.
According to your description, I think you can make some modifications in the table format, and create measures to get the latest status and count the number of process.
Like this:
Measure:
latest status = LASTNONBLANKVALUE ( pushdateset[UpdatedDate], SELECTEDVALUE ( pushdateset[State] ) )Created = COALESCE ( COUNTROWS ( FILTER ( SUMMARIZE ( pushdateset, pushdateset[IncidentId] ), [latest status] = "Created" ) ), 0 )If you have other questions, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try like
measure for status = lastnonblankvalue(Table[UpdatedDate], max(Table[status]))
Take max/last of UpdatedDate
ID as Group by/Axis
Hi Amitchandak,
Thanks for your quick reply, but I still have some question regarding to your solution:
1) by saying group by id, do you mean to put this column into visualization and use it as axis?
2) by saying take max of updated date, do you mean to get use latest updated date as filter?
Also, I need a summary table I mentioned before because I also need a visualization for:
1) Show the count of tickets of which InProgress is the latest state.
2) Show the count of tickets of which Created is the latest state.
Is there any way I can achieve this goal? Hoping you can help me out here, thank you very much!
