Creating a Calculated from a other columns

[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!

[v-xjiin-msft · Accepted solution]

Hi @CTW,

Same to your DAX formula, try following calculated column:

First Test Result =
CALCULATE (
    MAX ( data[Result] ),
    FILTER (
        data,
        data[TestTimeTest] = data[TestTimeFr]
            && 'Data'[S/N] = EARLIER ( 'Data'[S/N] )
            && data[station] = EARLIER ( 'Data'[station] )
    )
)

Thanks,
Xi Jin.