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

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

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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.

     

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

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