Vlookup next row on sorted table

[Anonymous]

Hi Community,

Looking for help for this problem. I have one table containing Visitor ID, Date/Time and Sensor.

I want to calculate which sensor the Visitor ID moved to next, so I can do a flow diagram (highlighted in yellow below)

Any ideas?

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

Try this calculated Column

Sensor to =
VAR NextTime =
    CALCULATE (
        MIN ( Table1[Time] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Date], Table1[Visitor_ID] ),
            Table1[Time] > EARLIER ( Table1[Time] )
        )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Table1[Sensor], 1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Date], Table1[Visitor_ID] ),
            Table1[Time] = NextTime
        )
    )

[Anonymous]

Thats fantastic! Thanks Zubair really appreciated. The logic makes sense so thankyou. I have marked as correct. Unfortunately I dont have enough memory on my machine to run it!

[v-xjiin-msft]

Hi @Anonymous,

I'm glad to hear that you have resolved your issue. Please kindly mark the corresponding reply. It will help others with relevant issue find the answer more easily.

Thanks,
Xi Jin.

[Anonymous]

Thats fantastic! Thanks Zubair really appreciated. The logic makes sense so thankyou. I have marked as correct. Unfortunately I dont have enough memory on my machine to run it!