Forum Discussion

CTW's avatar
CTW
Regular Visitor
8 years ago
Solved

Creating a Calculated from a other columns

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 11232/2/2018 13:00P2/2/2018 13:00P
terminator 11232/2/2018 13:15F2/2/2018 13:00P
terminator 11232/2/2018 15:00P2/2/2018 13:00P
terminator 11002/2/2018 14:00F2/2/2018 14:00F
terminator 11002/2/2018 14:20P2/2/2018 14:00F
terminator 11002/2/2018 14:21P2/2/2018 14:00F
terminator 11002/2/2018 14:30P2/2/2018 14:00F

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's avatar
    v-xjiin-msft
    8 years ago

    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.

1 Reply

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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.