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!