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, 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

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

     

4 Replies

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

     

  • 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