Forum Discussion

Dazagard's avatar
Dazagard
Regular Visitor
4 years ago
Solved

DAX Lookup in same table

Hi, I have been searching the forum for an answer, hopefully someone will be able to help me. I have a table containing production data, each item has a unique container ID, with multiple processes...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    There are a ton of different ways to do this. Here are a couple of drastically different possibilities.

     

    FirstProcess =
VAR MinDateTime =
    CALCULATE (
        MIN ( Table1[TransDateTransTime] ),
        ALLEXCEPT ( Table1, Table1[Comtainer ID] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( Table1[Process] ),
        ALLEXCEPT ( Table1, Table1[Comtainer ID] ),
        Table1[TransDateTransTime] = MinDateTime
    )

     

    FirstProcess = 
SELECTCOLUMNS (
    TOPN (
        1,
        FILTER ( Table1, Table1[Comtainer ID] = EARLIER ( Table1[Comtainer ID] ) ),
        Table1[TransDateTransTime], ASC
    ),
    "Process", Table1[Process]
)

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

There are a ton of different ways to do this. Here are a couple of drastically different possibilities.

 

FirstProcess =
VAR MinDateTime =
    CALCULATE (
        MIN ( Table1[TransDateTransTime] ),
        ALLEXCEPT ( Table1, Table1[Comtainer ID] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( Table1[Process] ),
        ALLEXCEPT ( Table1, Table1[Comtainer ID] ),
        Table1[TransDateTransTime] = MinDateTime
    )

 

FirstProcess = 
SELECTCOLUMNS (
    TOPN (
        1,
        FILTER ( Table1, Table1[Comtainer ID] = EARLIER ( Table1[Comtainer ID] ) ),
        Table1[TransDateTransTime], ASC
    ),
    "Process", Table1[Process]
)