Calculating First Time Pass Rate

[Anonymous]

Hi all,

I am trying to create a measure (but not precious if it needs to be a column) to calculate the weekly First Time Pass Rate for our product testing. This is based on a table containing individual product test results (see sample data below).

The first Time Pass Rate should be the number of products (each with a unique serial number) that pass the test first time divided by the total number of unique products.

From the data below the number of boards that pass first time is 6 and the total number of products tested is 10, so the first time pass rate should be 60%.

It can be assumed that once a product passes it will not be tested again. It can't however be assumed that a product will always pass at some point, sometimes they fail and are not retested (as in the example below serial number ARH1001293).

I have a "Start_time (bins)" group derived from "Start_time" which is groups of  7 days and I would ultimately like to plot data against this scale.

If anyone can help with the DAX that would be fantastic as I have not been able to find a way so far. 

[Veles]

I'm assuming here that you've created a dimension table filled with unique serial numbers that has a relationship to your data table which you can create using the new table command then

Serials = DISTINCT ( Data[Serial_Number]  )

Idea behind the below is it will go down your unique serials list and find the test status of the earliest test for that serial number and count it if it's a pass.

VAR SerialCount = DISTINCTCOUNT ( Serials[Serial_Number] )
VAR FirstPasses = SUMX ( Serials, IF ( CALCULATE ( MAX ( Data[Test_Status] ), FILTER ( Data, Data[Start_Time] = MIN( Data[Start_Time] ) ) ) = "PASSED", 1, 0 ) )
RETURN
FirstPasses / SerialCount

[Anonymous]

Hi Veles,

Many thanks for getting back to me so quickly. I have implemented what you suggested but it isnt quite working for me, probably my finger trouble being new to BI.

To find the issue I stripped back your code so I could just see the first time pass count. This seemed to be returning 0, so I stripped back the SUMX statement leaving the return value as below: 

VAR FirstPasses = IF ( CALCULATE ( MAX ( Combined_temp[Test_Status] ), FILTER ( Combined_temp, Combined_temp[Start_time] = MIN( Combined_temp[Start_time] ) ) ) = "PASSED", 1, 0 )

When I threw this into a table next to the serial numbers it looks like it is doing the right thing. There is a 1 for each serial number that was a first time pass. However when I put the SUMX around it and show the data with a weekly grouped start time the value is always 0, like its not summing the result of the IF. Any ideas?

[Anonymous · Accepted solution]

@Anonymous create the following and you should have your ans

FirstTime = CALCULATE(MIN(Data[Start_Time]), ALLEXCEPT(Data[Serial_Number])) 

FirstTimePassed = CALCULATE(MIN(Data[Start_Time]), ALLEXCEPT(Data[Serial_Number]), FITLER(Data[Test_Status] = "Passed"))

FirstTimePassedCount =

VAR SerialCount = Calculate(DISTINCTCOUNT(Data[Serial_Number], FILTER(ALL(Data)))

VAR PassedCount = Calculate(DISTINCTCOUNT(Data[Serial_Number], FILTER(Data, Data[FirstTime] = Data[FirstTimePassed]))   

Return PassedCount/SerialCount

[Anonymous]

Fantastic, many thanks. This did indeed solve the problem and I now have First Time Pass Rate being calculated.