Need help to calculate the First Pass Yield

[Sebastian123]

Hello everyone, I need help to calculate the first pass yield and I appreciate any help.  

In my Power BI project, I'm working with a table named "Tabelle1" which includes various columns, such as "Parameter," "Value," "Date," "PC," "Script," "Test," "ValueNUM," and "Test_ID." The values in the "Parameter" column can be "Testresult" or "SN_DUT," and the "Value" column can contain "Fail," "OK," or "null."

I would like to calculate the First Pass Yield (FPY) for my data. FPY is calculated as the ratio of "OK" test results to the total of both successful and failed tests. Specifically, I want to consider only the first test value for each unique "SN_DUT" number, identified based on the date and time. If there are multiple tests with the same "SN_DUT" number, I intend to use only the result of the first test.

Here an example: 

Parameter	Value	Date	PC	Script	Test	ValueNUM	Test_ID
Testresult	Fail	28.06.2023 14:06	PC1	Script1	test1	null	1445
SN_DUT	null	28.06.2023 14:06	PC1	Script1	test1	202326002	1445
Testresult	OK	28.06.2023 14:17	PC1	Script1	test2	null	1446
SN_DUT	null	28.06.2023 14:17	PC1	Script1	test2	202326003	1446
Testresult	Fail	28.06.2023 14:17	PC1	Script1	test3	null	1447
SN_DUT	null	28.06.2023 14:17	PC1	Script1	test3	202326004	1447
Testresult	OK	28.06.2023 14:17	PC1	Script1	test4	null	1448
SN_DUT	null	28.06.2023 14:17	PC1	Script1	test4	202326005	1448

In this example, the FPY should be 50%: 2*OK / (2*OK + 2*Fail) = 0.5

Another example: 

Parameter	Value	Date	PC	Script	Test	ValueNUM	Test_ID
Testresult	Fail	28.06.2023 14:06	PC1	Script1	test1	null	1445
SN_DUT	null	28.06.2023 14:06	PC1	Script1	test1	202326002	1445
Testresult	OK	28.06.2023 14:17	PC1	Script1	test2	null	1446
SN_DUT	null	28.06.2023 14:17	PC1	Script1	test2	202326002	1446
Testresult	Fail	30.06.2023 14:06	PC2	Script1	test3	null	1447
SN_DUT	null	30.06.2023 14:06	PC2	Script1	test3	202328003	1447
Testresult	OK	01.07.2023 14:06	PC3	Script1	test4	null	1448
SN_DUT	null	01.07.2023 14:06	PC3	Script1	test4	202328004	1448
Testresult	Fail	01.07.2023 14:07	PC3	Script1	test5	null	1448
SN_DUT	null	01.07.2023 14:07	PC3	Script1	test5	202328004	1448

In this example, the FPY should be 33%: 1*OK / (1*OK + 2*Fail) = 0.33.

I'm thankfull for every help.

[Anonymous · Accepted solution]

Hi @Sebastian123 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

Index = RANKX('Table',[Test_ID],,ASC,Dense)

(3)We can create measures. 

Measure = var a=SUMMARIZE(FILTER(ALL('Table'),CONTAINSSTRING([Parameter],"SN")),[Index],[ValueNUM],[Parameter],"TestResult",MINX(FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])&&[Parameter]="Testresult"),[Value]),"Date",MINX(FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])&&[Parameter]="Testresult"),[Date]))
var b=MINX(FILTER(a,[ValueNUM] in VALUES('Table'[ValueNUM])),[Date])
var c=MINX(FILTER(a,[ValueNUM] in VALUES('Table'[ValueNUM])&&[TestResult]="OK"),[Date])
return COUNTROWS(SUMMARIZE(FILTER(a,[ValueNUM] in VALUES('Table'[ValueNUM])&&b=c),[ValueNUM]))

Measure 2 = var a=SUMX(VALUES('Table'[ValueNUM]),[Measure])
return DIVIDE(a,CALCULATE(DISTINCTCOUNT('Table'[ValueNUM]),CONTAINSSTRING('Table'[Parameter],"SN")))

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous · Accepted solution]

Hi @Sebastian123 ,

Update the rank column.

Index = RANKX('Table',[Test],,ASC,Dense)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous · Accepted solution]

Hi @Sebastian123 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

Index = RANKX('Table',[Test_ID],,ASC,Dense)

(3)We can create measures. 

Measure = var a=SUMMARIZE(FILTER(ALL('Table'),CONTAINSSTRING([Parameter],"SN")),[Index],[ValueNUM],[Parameter],"TestResult",MINX(FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])&&[Parameter]="Testresult"),[Value]),"Date",MINX(FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])&&[Parameter]="Testresult"),[Date]))
var b=MINX(FILTER(a,[ValueNUM] in VALUES('Table'[ValueNUM])),[Date])
var c=MINX(FILTER(a,[ValueNUM] in VALUES('Table'[ValueNUM])&&[TestResult]="OK"),[Date])
return COUNTROWS(SUMMARIZE(FILTER(a,[ValueNUM] in VALUES('Table'[ValueNUM])&&b=c),[ValueNUM]))

Measure 2 = var a=SUMX(VALUES('Table'[ValueNUM]),[Measure])
return DIVIDE(a,CALCULATE(DISTINCTCOUNT('Table'[ValueNUM]),CONTAINSSTRING('Table'[Parameter],"SN")))

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Sebastian123]

Hello Neeko Tang,

Thank you for your prompt response. I apologize for any confusion earlier. I have revised the task description to provide more clarity. 

Unfortunatley it is not possible to upload the pbix file 

Thank your for help! 

[Anonymous · Accepted solution]

Hi @Sebastian123 ,

Update the rank column.

Index = RANKX('Table',[Test],,ASC,Dense)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Sebastian123]

Thanks, this worked for my test data, but not for my main data. As soon as I want to display it, it says: Display exceeds available resources.
Is there any way to improve the performance?

[Anonymous · Accepted solution]

Hi @Sebastian123 ,

You can create a index column in power query. His performance will be better than creating a calculated column in desktop.

Split the [Test] column.

If that doesn't help, you can use performance analyzer tools to measure and optimize your DAX queries. You can use tools like Performance Analyzer in Power BI Desktop, or DAX Studio to analyze the duration and execution plan of each visual in your report. These tools can help you identify which queries are taking the longest time to run, and how to improve them.

In addition to this you can optimize the model and filter the data to increase performance.

Please refer :

DAX Best Practices | MAQ Software Insights

Power BI Performance Optimization Tips (mssqltips.com)

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.