Forum Discussion
Solved
Summarise table Streaming dataset
Hello! -Need some help with getting a KPI. -Working with a Streaming dataset so I can't create tables. -I've got a dataset that looks like this: -In the movement column 1=in, 0=out. -Only ...
Hi rolopezteteruk,
You can try to use the following calculate column formula on your table to get 'type of ship' based on the current 'Place' and 'Date':
formula = VAR maxDate = CALCULATE ( MAX ( Table[DATE] ), FILTER ( Table, Table[PLACE] = EARLIER ( Table[PLACE] ) ) ) RETURN IF ( Table[Date] = maxDate && Table[MOVEMENT(IN/OUT)] = 1, Table[TYPE OF SHIP], "" )Regards,
Xiaoxin Sheng
Hi Ashish!
The results are : 5 ships type 1, 3 ships type 2, 4 ships type 3
Tell me if you can't copy this I'll send it some other way.
The way I solve this in excel is:=IF(IF(MAXIFS($D$2:$D$133,$A$2:$A$133,A2)=D2,B2,"")=1,E2,"") But I don't know how to solve this in power bi with the streaming dataset, I need to show the result in a data card.
| PLACE | MOVEMENT(IN/OUT) | SHIP CODE | DATE | TYPE OF SHIP |
| A2 | 0 | 7436 | 9/19/2022 10:36 | 3 |
| A4 | 0 | 1874 | 9/19/2022 22:36 | 3 |
| B2 | 0 | 3530 | 9/20/2022 10:36 | 3 |
| B4 | 0 | 8032 | 9/20/2022 22:36 | 3 |
| A5 | 0 | 9634 | 9/21/2022 10:36 | 3 |
| A3 | 0 | 7341 | 9/21/2022 22:36 | 2 |
| C5 | 0 | 6896 | 9/22/2022 10:36 | 1 |
| C3 | 0 | 2150 | 9/22/2022 22:36 | 2 |
| C2 | 0 | 6271 | 9/23/2022 10:36 | 3 |
| A7 | 0 | 1889 | 9/23/2022 22:36 | 2 |
| A5 | 1 | 2443 | 9/24/2022 10:36 | 2 |
| C3 | 1 | 4237 | 9/24/2022 22:36 | 3 |
| C6 | 0 | 8305 | 9/25/2022 10:36 | 1 |
| B5 | 0 | 1997 | 9/25/2022 22:36 | 1 |
| C4 | 0 | 5198 | 9/26/2022 10:36 | 2 |
| A5 | 0 | 8368 | 9/26/2022 22:36 | 3 |
| C3 | 0 | 8119 | 9/27/2022 10:36 | 2 |
| C8 | 0 | 5279 | 9/27/2022 22:36 | 3 |
| A7 | 1 | 5273 | 9/28/2022 10:36 | 3 |
| C6 | 1 | 2238 | 9/28/2022 22:36 | 3 |
| B8 | 0 | 8698 | 9/29/2022 10:36 | 2 |
| B2 | 1 | 1016 | 9/29/2022 22:36 | 3 |
| A6 | 0 | 7221 | 9/30/2022 10:36 | 3 |
| C1 | 0 | 4369 | 9/30/2022 22:36 | 1 |
| B4 | 1 | 5533 | 10/1/2022 10:36 | 1 |
| C7 | 0 | 2886 | 10/1/2022 22:36 | 2 |
| A6 | 1 | 2124 | 10/2/2022 10:36 | 1 |
| B1 | 0 | 4305 | 10/2/2022 22:36 | 2 |
| C3 | 1 | 9627 | 10/3/2022 10:36 | 1 |
| A10 | 0 | 6480 | 10/3/2022 22:36 | 2 |
| C9 | 0 | 3908 | 10/4/2022 10:36 | 1 |
| A6 | 0 | 2696 | 10/4/2022 22:36 | 1 |
| A4 | 1 | 4762 | 10/5/2022 10:36 | 1 |
| A9 | 0 | 2277 | 10/5/2022 22:36 | 3 |
| C10 | 0 | 5566 | 10/6/2022 10:36 | 3 |
| B7 | 0 | 9017 | 10/6/2022 22:36 | 2 |
| A5 | 1 | 3761 | 10/7/2022 10:36 | 3 |
| A7 | 0 | 5907 | 10/7/2022 22:36 | 1 |
| A7 | 1 | 5618 | 10/8/2022 10:36 | 1 |
| B10 | 0 | 5296 | 10/8/2022 22:36 | 1 |
| B7 | 1 | 4425 | 10/9/2022 10:36 | 3 |
| C1 | 1 | 6105 | 10/9/2022 22:36 | 2 |
| C9 | 1 | 3477 | 10/10/2022 10:36 | 1 |
| A3 | 1 | 4670 | 10/10/2022 22:36 | 3 |
| B6 | 0 | 3873 | 10/11/2022 10:36 | 2 |
| B9 | 0 | 3404 | 10/11/2022 22:36 | 2 |
| B8 | 1 | 4057 | 10/12/2022 10:36 | 2 |
| C10 | 1 | 3218 | 10/12/2022 22:36 | 1 |
| B9 | 1 | 4560 | 10/13/2022 10:36 | 2 |
| B9 | 0 | 4091 | 10/13/2022 22:36 | 1 |
| B9 | 1 | 7614 | 10/14/2022 10:36 | 1 |
| C2 | 1 | 1161 | 10/14/2022 22:36 | 3 |
| C9 | 0 | 1331 | 10/15/2022 10:36 | 3 |
| C6 | 0 | 1895 | 10/15/2022 22:36 | 3 |
| A1 | 0 | 5606 | 10/16/2022 10:36 | 2 |
| B3 | 0 | 6052 | 10/16/2022 22:36 | 3 |
| C9 | 1 | 6894 | 10/17/2022 10:36 | 2 |
| B3 | 1 | 8451 | 10/17/2022 22:36 | 1 |
| A3 | 0 | 4068 | 10/18/2022 10:36 | 3 |
| C4 | 1 | 5666 | 10/18/2022 22:36 | 3 |
| B5 | 1 | 9759 | 10/19/2022 10:36 | 2 |
| C7 | 1 | 9552 | 10/19/2022 22:36 | 3 |
| A8 | 0 | 8554 | 10/20/2022 10:36 | 1 |
| A3 | 1 | 4162 | 10/20/2022 22:36 | 3 |
| A4 | 0 | 4970 | 10/21/2022 10:36 | 3 |
| C7 | 0 | 5276 | 10/21/2022 22:36 | 3 |
| A9 | 1 | 8267 | 10/22/2022 10:36 | 3 |
| C4 | 0 | 5226 | 10/22/2022 22:36 | 1 |
| B5 | 0 | 8590 | 10/23/2022 10:36 | 3 |
| A9 | 0 | 7202 | 10/23/2022 22:36 | 1 |
| A5 | 0 | 7000 | 10/24/2022 10:36 | 1 |
| A7 | 0 | 2603 | 10/24/2022 22:36 | 1 |
| A7 | 1 | 8509 | 10/25/2022 10:36 | 2 |
| C6 | 1 | 2551 | 10/25/2022 22:36 | 3 |
| B1 | 1 | 6146 | 10/26/2022 10:36 | 3 |
| B3 | 0 | 2541 | 10/26/2022 22:36 | 2 |
| C7 | 1 | 9588 | 10/27/2022 10:36 | 3 |
| C6 | 0 | 9301 | 10/27/2022 22:36 | 2 |
| C1 | 0 | 2028 | 10/28/2022 10:36 | 2 |
| B4 | 0 | 2341 | 10/28/2022 22:36 | 1 |
| B1 | 0 | 6535 | 10/29/2022 10:36 | 3 |
| A1 | 1 | 2217 | 10/29/2022 22:36 | 3 |
| A1 | 0 | 1519 | 10/30/2022 10:36 | 3 |
| B9 | 0 | 7780 | 10/30/2022 22:36 | 3 |
| B8 | 0 | 6251 | 10/31/2022 10:36 | 2 |
| A7 | 0 | 2075 | 10/31/2022 22:36 | 3 |
| A10 | 1 | 5840 | 11/1/2022 10:36 | 1 |
| A10 | 0 | 6542 | 11/1/2022 22:36 | 1 |
| A1 | 1 | 7610 | 11/2/2022 10:36 | 1 |
| A6 | 1 | 5719 | 11/2/2022 22:36 | 2 |
| A10 | 1 | 6205 | 11/3/2022 10:36 | 3 |
| B4 | 1 | 6581 | 11/3/2022 22:36 | 3 |
| C10 | 0 | 9092 | 11/4/2022 10:36 | 1 |
| A3 | 0 | 3872 | 11/4/2022 22:36 | 3 |
| C7 | 0 | 2063 | 11/5/2022 10:36 | 2 |
| B7 | 0 | 5760 | 11/5/2022 22:36 | 2 |
| C2 | 0 | 2536 | 11/6/2022 10:36 | 3 |
| B7 | 1 | 3936 | 11/6/2022 22:36 | 1 |
| A9 | 1 | 1465 | 11/7/2022 10:36 | 1 |
| A7 | 1 | 6465 | 11/7/2022 22:36 | 1 |
| A9 | 0 | 4940 | 11/8/2022 10:36 | 2 |
| C2 | 1 | 3674 | 11/8/2022 22:36 | 1 |
| B10 | 1 | 6736 | 11/9/2022 10:36 | 1 |
| A7 | 0 | 5359 | 11/9/2022 22:36 | 2 |
| C2 | 0 | 4951 | 11/10/2022 10:36 | 3 |
| A5 | 1 | 7401 | 11/10/2022 22:36 | 2 |
| B5 | 1 | 5637 | 11/11/2022 10:36 | 2 |
| B4 | 0 | 1271 | 11/11/2022 22:36 | 3 |
| C10 | 1 | 7953 | 11/12/2022 10:36 | 2 |
| B5 | 0 | 4264 | 11/12/2022 22:36 | 2 |
| A1 | 0 | 7010 | 11/13/2022 10:36 | 3 |
| B7 | 0 | 8372 | 11/13/2022 22:36 | 2 |
| C6 | 1 | 9781 | 11/14/2022 10:36 | 3 |
| C2 | 1 | 7073 | 11/14/2022 22:36 | 2 |
| C6 | 0 | 3368 | 11/15/2022 10:36 | 3 |
| C6 | 1 | 2771 | 11/15/2022 22:36 | 2 |
| C8 | 1 | 4907 | 11/16/2022 10:36 | 1 |
| B1 | 1 | 2264 | 11/16/2022 22:36 | 1 |
| B7 | 1 | 3274 | 11/17/2022 10:36 | 3 |
| C4 | 1 | 9962 | 11/17/2022 22:36 | 1 |
| B6 | 1 | 7957 | 11/18/2022 10:36 | 1 |
| C8 | 0 | 1629 | 11/18/2022 22:36 | 1 |
| A8 | 1 | 8728 | 11/19/2022 10:36 | 3 |
| C10 | 0 | 5798 | 11/19/2022 22:36 | 1 |
| C8 | 1 | 9208 | 11/20/2022 10:36 | 1 |
| C6 | 0 | 3892 | 11/20/2022 22:36 | 3 |
| A8 | 0 | 7106 | 11/21/2022 10:36 | 2 |
| C3 | 0 | 9467 | 11/21/2022 22:36 | 3 |
| C6 | 1 | 5333 | 11/22/2022 10:36 | 1 |
| B6 | 0 | 7747 | 11/22/2022 22:36 | 2 |
| C2 | 0 | 2750 | 11/23/2022 10:36 | 1 |
| A3 | 1 | 1271 | 11/23/2022 22:36 | 3 |
Hi,
Thank you for sharing your data and the Excel formula. I have really not been able to understand your requirement. May be someone else will help you.