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