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]
)

     

Dazagard's avatar
Dazagard
Regular Visitor
4 years ago

Thanks very much for your solutions, the first worked perfectly, I am slowly making the transition from Excel to Power BI, and Im unfamiliar with VAR rather than just copy and paste would you explain how the expression works so I can hopefully understand it apply the logic elsewhere when needed.

 

Thanks again for your help 

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