to show value in calculated column based on another column (lookup?)

[CTW]

Hi DAX professionals:

I've run into a issue where i'm trying to create a calculated column based off of other columns. please refer to the below screen shot to what my issue is:

Station                           S/N        TestTimeTest        Result             TestTimeFr        First Test result

terminator 1	123	2/2/2018 13:00	P	2/2/2018 13:00	P
terminator 1	123	2/2/2018 13:15	F	2/2/2018 13:00	P
terminator 1	123	2/2/2018 15:00	P	2/2/2018 13:00	P
terminator 1	100	2/2/2018 14:00	F	2/2/2018 14:00	F
terminator 1	100	2/2/2018 14:20	P	2/2/2018 14:00	F
terminator 1	100	2/2/2018 14:21	P	2/2/2018 14:00	F
terminator 1	100	2/2/2018 14:30	P	2/2/2018 14:00	F

I am trying to calculate column "First Test Result"  which fills the column with the very first test result value for that serial number.

i managed to calculate the column with the TestTime of the first test by using this DAX formula:

TestTimeFr = CALCULATE ( MIN ( 'Data'[TestTime]), FILTER ( 'data', 'Data'[S/N] = EARLIER ( 'Data'[S/N] ) && data[station] = EARLIER ( 'Data'[station] ) ) )

Now I just need some help to input the test result for the TestTimeFr in the column to get the First Test Result.

Any help is greatly appreciated!

[Zubair_Muhammad · Accepted solution]

Hi @CTW

Try this column

First Test Result=
CALCULATE (
    FIRSTNONBLANK ( Data[Result], 1 ),
    FILTER (
        ALLEXCEPT ( Data, Data[Station], Data[S/N] ),
        Data[TestTimeTest] = Data[TestTimeFr]
    )
)