Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rolopezteteruk
Frequent Visitor

Summarise table Streaming dataset

‎09-16-2022 11:58 AM

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:

rolopezteteruk_1-1663353971828.png

-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

Labels:
Message 1 of 9
561 Views
0
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support
In response to rolopezteteruk

‎09-19-2022 11:03 PM

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Message 9 of 9
442 Views
0
8 REPLIES 8
Ashish_Mathur
Super User
Super User

‎09-16-2022 05:08 PM

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 9
496 Views
0
rolopezteteruk
Frequent Visitor
In response to Ashish_Mathur

‎09-19-2022 06:56 AM

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.

PLACEMOVEMENT(IN/OUT)SHIP CODEDATETYPE OF SHIP
A2074369/19/2022 10:363
A4018749/19/2022 22:363
B2035309/20/2022 10:363
B4080329/20/2022 22:363
A5096349/21/2022 10:363
A3073419/21/2022 22:362
C5068969/22/2022 10:361
C3021509/22/2022 22:362
C2062719/23/2022 10:363
A7018899/23/2022 22:362
A5124439/24/2022 10:362
C3142379/24/2022 22:363
C6083059/25/2022 10:361
B5019979/25/2022 22:361
C4051989/26/2022 10:362
A5083689/26/2022 22:363
C3081199/27/2022 10:362
C8052799/27/2022 22:363
A7152739/28/2022 10:363
C6122389/28/2022 22:363
B8086989/29/2022 10:362
B2110169/29/2022 22:363
A6072219/30/2022 10:363
C1043699/30/2022 22:361
B41553310/1/2022 10:361
C70288610/1/2022 22:362
A61212410/2/2022 10:361
B10430510/2/2022 22:362
C31962710/3/2022 10:361
A100648010/3/2022 22:362
C90390810/4/2022 10:361
A60269610/4/2022 22:361
A41476210/5/2022 10:361
A90227710/5/2022 22:363
C100556610/6/2022 10:363
B70901710/6/2022 22:362
A51376110/7/2022 10:363
A70590710/7/2022 22:361
A71561810/8/2022 10:361
B100529610/8/2022 22:361
B71442510/9/2022 10:363
C11610510/9/2022 22:362
C91347710/10/2022 10:361
A31467010/10/2022 22:363
B60387310/11/2022 10:362
B90340410/11/2022 22:362
B81405710/12/2022 10:362
C101321810/12/2022 22:361
B91456010/13/2022 10:362
B90409110/13/2022 22:361
B91761410/14/2022 10:361
C21116110/14/2022 22:363
C90133110/15/2022 10:363
C60189510/15/2022 22:363
A10560610/16/2022 10:362
B30605210/16/2022 22:363
C91689410/17/2022 10:362
B31845110/17/2022 22:361
A30406810/18/2022 10:363
C41566610/18/2022 22:363
B51975910/19/2022 10:362
C71955210/19/2022 22:363
A80855410/20/2022 10:361
A31416210/20/2022 22:363
A40497010/21/2022 10:363
C70527610/21/2022 22:363
A91826710/22/2022 10:363
C40522610/22/2022 22:361
B50859010/23/2022 10:363
A90720210/23/2022 22:361
A50700010/24/2022 10:361
A70260310/24/2022 22:361
A71850910/25/2022 10:362
C61255110/25/2022 22:363
B11614610/26/2022 10:363
B30254110/26/2022 22:362
C71958810/27/2022 10:363
C60930110/27/2022 22:362
C10202810/28/2022 10:362
B40234110/28/2022 22:361
B10653510/29/2022 10:363
A11221710/29/2022 22:363
A10151910/30/2022 10:363
B90778010/30/2022 22:363
B80625110/31/2022 10:362
A70207510/31/2022 22:363
A101584011/1/2022 10:361
A100654211/1/2022 22:361
A11761011/2/2022 10:361
A61571911/2/2022 22:362
A101620511/3/2022 10:363
B41658111/3/2022 22:363
C100909211/4/2022 10:361
A30387211/4/2022 22:363
C70206311/5/2022 10:362
B70576011/5/2022 22:362
C20253611/6/2022 10:363
B71393611/6/2022 22:361
A91146511/7/2022 10:361
A71646511/7/2022 22:361
A90494011/8/2022 10:362
C21367411/8/2022 22:361
B101673611/9/2022 10:361
A70535911/9/2022 22:362
C20495111/10/2022 10:363
A51740111/10/2022 22:362
B51563711/11/2022 10:362
B40127111/11/2022 22:363
C101795311/12/2022 10:362
B50426411/12/2022 22:362
A10701011/13/2022 10:363
B70837211/13/2022 22:362
C61978111/14/2022 10:363
C21707311/14/2022 22:362
C60336811/15/2022 10:363
C61277111/15/2022 22:362
C81490711/16/2022 10:361
B11226411/16/2022 22:361
B71327411/17/2022 10:363
C41996211/17/2022 22:361
B61795711/18/2022 10:361
C80162911/18/2022 22:361
A81872811/19/2022 10:363
C100579811/19/2022 22:361
C81920811/20/2022 10:361
C60389211/20/2022 22:363
A80710611/21/2022 10:362
C30946711/21/2022 22:363
C61533311/22/2022 10:361
B60774711/22/2022 22:362
C20275011/23/2022 10:361
A31127111/23/2022 22:363

 

Message 7 of 9
469 Views
0
v-shex-msft
Community Support
Community Support
In response to rolopezteteruk

‎09-19-2022 11:03 PM

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 9 of 9
443 Views
0
Ashish_Mathur
Super User
Super User
In response to rolopezteteruk

‎09-19-2022 04:30 PM

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 8 of 9
457 Views
0
parry2k
Super User
Super User

‎09-16-2022 01:52 PM

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 4 of 9
515 Views
0
rolopezteteruk
Frequent Visitor
In response to parry2k

‎09-16-2022 02:02 PM
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!
Message 5 of 9
508 Views
0
parry2k
Super User
Super User

‎09-16-2022 01:01 PM

@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 YouTube.gif 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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 2 of 9
525 Views
0
rolopezteteruk
Frequent Visitor
In response to parry2k

‎09-16-2022 01:46 PM

Sorry that is not working

Message 3 of 9
518 Views
0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

View All