Forum Discussion

rolopezteteruk's avatar
rolopezteteruk
Frequent Visitor
3 years ago
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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Ashish_Mathur
Super User
3 years ago

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's avatar
rolopezteteruk
Frequent Visitor
3 years ago

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

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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