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

VAR is used to calculate an expression so that it can be re-used. It's super useful for making DAX more readable than Excel formulas.

 

At a high level, the measure calculates the minimal datetime for that ID and then looks up the process corresponding to that minimal datetime.

 

Note that ALLEXCEPT is used to remove all of the filter context, except for the ID, which exists due to a context transition from the row context to filter context.

Dazagard's avatar
Dazagard
Regular Visitor
4 years ago

Thanks for taking the time to reply and explain, I really appreciate your help