Summarise table Streaming dataset

[rolopezteteruk]

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 one ship in each space, at a time.

***I need to show how many ships type 2 are in the entire space(A1+A2+A3...) every moment.**

(I was thinking first I need to summarize making groups by spaces, then get the last datetime and see what was the movement at that time (0 no ship, 1 ship there), and then count how many boats are there (1) and are

 type 2)

SAVE ME PLEASE! HAHA

[Ashish_Mathur]

Hi,

Share a larger dataset and show the expected result.  Share data in a form that can be pasted in an MS Excel file.

[rolopezteteruk]

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

[v-shex-msft · Accepted solution]

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

[Ashish_Mathur]

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.

[parry2k]

@rolopezteteruk from your reply, it is hard to say what have you tried. Are you getting errors/wrong results? Share more details, just saying "not working", doesn't help anyone.

[rolopezteteruk]

i've tried this:

mostrecentq =

SUMX(

    SUMMARIZE(

        IoTSignals,

        IoTSignals[position],

        "@Major",

        CALCULATE(

            SUM(CONVERT(IoTSignals[movement];INTERGER)),

            TOPN(

                1,

                ALLEXCEPT(IoTSignals,IoTSignals[position]),

                CALCULATE(MAX(IoTSignals[EventEnqueuedUtcTime])),

                DESC

            )

        )

    ),

[@Major]

)

I'm trying to visualize it in a KPI card, and it shows blank, but I've got many "ships", also, I've noticed that my "movement column" has string values, but I can't convert it in the formula. Thanks for your quick response!

[parry2k]

@rolopezteteruk check these two video my YT channel and that will get you going

How to get value of each product based on the most recent transaction - Power BI - YouTube

How to compare the most recent price change for each product without Rank function - YouTube

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[rolopezteteruk]

Sorry that is not working