How to get and COUNT items tested excluding only the first test.

[jrmax]

Hi guys, we have a table like the one below with multiple serials tested. We want to know how to create a measure for:
- All serial_numbers re-tested
- Count of Serial_numbers re-tested
- SUM of the column -TestDuration- for re-rest only

We got the First/Last items tested and -First Pass Yield- calculation, but we are now stuck on these new measures for re-tests.

Any advice will be appreciated.

Thank you!

JR

[v-shex-msft · Accepted solution]

Hi @jrmax,

I think you can add a variable to get the first date from your table based on current category, then you can and add an if statement to compare current date and the first date and use this as condition in calculation formula.

If you confused about coding formula, please share some dummy data to test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[jrmax]

I found it !!, thank you @v-shex-msft, for the tip; a small piece of advice makes a huge difference. 

Here is the code that worked for me:

First Test Date In Timeframe =
// Returns the first test date in
// the current context.
IF( HASONEFILTER( 'Logs'[Serial_Number] ),
      var __serialN = SELECTEDVALUE( 'Logs'[Serial_Number] )
      var __firstTestDate =
                          MINX(
                               CALCULATETABLE(
                                               'Logs',
                                               'Logs'[Serial_Number] = __serialN,
                                               ALLSELECTED('Logs')
                                              ),
                                              'Logs'[testDateTime]
                              )
return
__firstTestDate


Retest Count In Timeframe =
// Returns the count of SNs retested
// in the current timeframe.
var __retestCountResult =
                          SUMX(
                               VALUES('Logs'[testDateTime]),
                               IF('Logs'[testDateTime]>[First Test Date In Timeframe],
                                  1,
                                  0
                                   )
                                )
return
       IF( __retestCountResult > 0, __retestCountResult )


Retest Time Duration in Timeframe =
// Returns the retest time duration of
// SNs.
var __retestDurationTime =
                          SUMX(
                                ALLSELECTED('Logs'),
                                IF('Logs'[testDateTime]>[First Test Date In Timeframe],
                                    VALUE('Logs'[testDuration]),
                                    0
                                   )
                                )
return
IF( __retestDurationTime > 0, FORMAT(__retestDurationTime,"HH:MM:SS" ))

[jrmax]

Thank you, @v-shex-msft! Here is sample data; a formula will be helpful... thanks!!

Comment	Serial_Number	testDateTime	testDuration	finalTestResult
FirstTest	SN0895611	11/11/2022 12:18:03	02:19.1	Fail
FirstTest	SN0731214	11/11/2022 12:18:48	02:19.4	Fail
Re-test	SN0895611	11/11/2022 12:21:26	03:51.3	Pass
Re-test	SN0731214	11/11/2022 12:21:36	05:02.5	Fail
FirstTest	SN0441212	11/11/2022 12:22:29	03:50.9	Fail
FirstTest	SN0423612	11/11/2022 12:22:40	03:50.9	Fail
Re-test	SN0423612	11/11/2022 12:23:40	02:21.1	Fail
Re-test	SN0423612	11/11/2022 12:26:34	03:51.8	Pass
Re-test	SN0441212	11/11/2022 12:27:07	03:50.3	Fail
Re-test	SN0731214	11/11/2022 12:27:47	03:54.7	Pass

[amitchandak]

@jrmax ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[jrmax]

Thank you @amitchandak 

EDITED adding a more context and picture: 

Given the previous table, a Retest is a SerialNo tested more than once, so in this case, SN200 and SN300 (with the red arrow) are considered retests.

We want to know how many retests were made, in this case it should be equal to: 4 and the SUM of column TestDuration

Thank you again!, we really appreciate any help on this.