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

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!