DAX Lookup in same table

[Dazagard]

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, each process has a transaction Date and Time I have created a concatenated Date & Time column so the earliest process can be identified,

I need a column that shows the first process for each container ID but I'm not sure how to achieve this

Below is a sample of data and the expected result column i would like.

Comtainer ID	Process	Transaction Date	Transaction Time	TransDateTransTime	Pack Ref	First Process
164823	TRANTC	20211021	155500	20211021155500	50696164823	SAW2
164823	PK2	20211018	202500	20211018202500	50696164823	SAW2
164823	QATBMT	20211018	182600	20211018182600	50696164823	SAW2
164823	QATB	20211017	182100	20211017182100	50696164823	SAW2
164823	AGE2	20211017	182000	20211017182000	50696164823	SAW2
164823	SAW2	20211017	140300	20211017140300	50696164823	SAW2

[Dazagard]

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]

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]

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

[AlexisOlson · Accepted solution]

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