DAX: Testresult within first 60mins

[vincentakatoh]

Hi,

Need help on DAX to determine the latest TestResult within the first 60mins (highlighted in yellow). Sample data as below.

Sample table (Final TestResults may or maynot exceed 60mins)

 https://1drv.ms/x/s!ArjVwEnHONXNgiLUqh7vnuL6iSnC

DAX use to calculate TestResult Final and TestResult First

TestTimeFr = CALCULATE ( MIN ( 'Data'[TestTime_TC1]); FILTER ( 'data'; 'Data'[SerialNumber] = EARLIER ( 'Data'[SerialNumber] ) && data[station] = EARLIER ( 'Data'[station] ) ) )

TestResultsFr = IF(Data[TestTime]=Data[TestTimeFr];Data[TestResult];"null")

TestTimeFn = CALCULATE ( MAX ( 'Data'[TestTime]); FILTER ( 'data'; 'Data'[SerialNumber] = EARLIER ( 'Data'[SerialNumber] ) && data[station] = EARLIER ( 'Data'[station] ) ) )

TestResultsFn = IF(Data[TestTime]=Data[TestTimeFn];Data[TestResult];"null")

[v-huizhn-msft · Accepted solution]

Hi @vincentakatoh,

Please create calculated columns using the formula below.

duration =
IF ( DATEDIFF ( Data[TestTimeFr], Data[TestTime_TC1], MINUTE ) < 60, 1, 0 )

latest TestResult within the first 60mins =
CALCULATE ( MAX ( Data[TestTime_TC1] ), FILTER ( Data, Data[duration] ) )

You will get the following expected result.



Best Regards,
Angelia

[vincentakatoh]

Hi @v-huizhn-msft, 

Thanks. Works pefectly on my actual data. I'm only able to do so much with PowerBI because all the great folks in this forum.

Super Kudos!