Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
From the data table, I need to calculate the first pass yield based on Serial Number.
One serial number has many Result, but I'am only intrested if the serial number has a defect but i want to know the procentage.
In this case I wan't the result to show 50% FPY since serialnumber 2222222 has a defect and serialnumber 44444444 has 0 defects.
How should i write the DAX formulation?
Serial number | Reference | Pin nr | Volume | Result |
2222222 | C1 | 1 | 56 | Defect |
2222222 | C1 | 2 | 55 | Defect |
2222222 | C3 | 1 | 103 | GooD |
2222222 | C3 | 2 | 104 | GooD |
2222222 | C5 | 1 | 105 | False Call |
2222222 | C5 | 2 | 106 | GooD |
2222222 | C7 | 1 | 107 | False Call |
2222222 | C7 | 2 | 108 | GooD |
2222222 | C9 | 1 | 30 | Defect |
2222222 | C9 | 2 | 0 | Defect |
4444444 | C1 | 1 | 103 | GooD |
4444444 | C1 | 2 | 103 | GooD |
4444444 | C3 | 1 | 103 | GooD |
4444444 | C3 | 2 | 104 | GooD |
4444444 | C5 | 1 | 105 | GooD |
4444444 | C5 | 2 | 106 | GooD |
4444444 | C7 | 1 | 107 | GooD |
4444444 | C7 | 2 | 108 | GooD |
4444444 | C9 | 1 | 108 | GooD |
4444444 | C9 | 2 | 108 | GooD |
Solved! Go to Solution.
Hi @TcT85 ,
Thanks for your reply and clarification.
How about this:
Here comes the measure:
TomsFPYMeasure = VAR _helpTable = SUMMARIZE ( 'Table', 'Table'[Serialnumber], "SerialNumberCount", DISTINCTCOUNT ( 'Table'[Serialnumber] ), "DefectYesNo", IF ( CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Result] = "Defect" ) > 0, 0, 1 ) ) RETURN DIVIDE ( SUMX ( _helpTable, [DefectYesNo] ) ,SUMX ( _helpTable, [SerialNumberCount] ) )
Does this work for you? 🙂
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @TcT85 ,
Could you provide the data and maybe an outcome exactly of what you would like to achieve?
I feel like I would only be guessing right now 🙂
Thanks!
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi Tom,
Sorry for lack of information.
I would like to recieve the Firstpass yield based on a formula with Serialnumber and Result.
Total FPY for all products |
50% |
and
Product | FPY |
A | 50% |
B | 100% |
C | 0% |
Here is the set of data:
Product | Serialnumber | Reference | Pin Nr | Volume | Result |
A | 1111 | C1 | 1 | 56 | Defect |
A | 1111 | C1 | 2 | 55 | Defect |
A | 1111 | C2 | 1 | 103 | Good |
A | 1111 | C2 | 2 | 104 | Good |
A | 2222 | C1 | 1 | 105 | Good |
A | 2222 | C1 | 2 | 106 | Good |
A | 2222 | C2 | 1 | 107 | Good |
A | 2222 | C2 | 2 | 108 | Good |
B | 3333 | C1 | 1 | 102 | Good |
B | 3333 | C1 | 2 | 103 | Good |
B | 3333 | C2 | 1 | 101 | Good |
B | 3333 | C2 | 2 | 102 | Good |
C | 4444 | C1 | 1 | 30 | Defect |
C | 4444 | C1 | 2 | 0 | Defect |
C | 4444 | C2 | 1 | 103 | Good |
C | 4444 | C2 | 2 | 102 | Good |
But with your formula I get this result.
The product C need to show 0% and product B need to show 100%
Hi @TcT85 ,
Thanks for your reply and clarification.
How about this:
Here comes the measure:
TomsFPYMeasure = VAR _helpTable = SUMMARIZE ( 'Table', 'Table'[Serialnumber], "SerialNumberCount", DISTINCTCOUNT ( 'Table'[Serialnumber] ), "DefectYesNo", IF ( CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Result] = "Defect" ) > 0, 0, 1 ) ) RETURN DIVIDE ( SUMX ( _helpTable, [DefectYesNo] ) ,SUMX ( _helpTable, [SerialNumberCount] ) )
Does this work for you? 🙂
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi Tomfox,
This is working perfectly, I got excatly what I was looking for. 😀
But can you please explain the red marked area, how this works?
Just in case i would copy this formula for some other cases.
I appreciate your time, patience and expertize, big thanks! 😀
Hi @TcT85 ,
Awesome that I could help you! 🙂
The marked part meets the statements of the if clause: The logical test condition, the result to be returned if the logical test is TRUE (in our case: 0) and the result to be returned if the logical test is FALSE (in our case: 1)
The logical test checks whether there is a "defect" row for each serial number. If there is one, then we return 0, otherwise 1. This is how the helpTable looks like:
Later, we could just use these columns to divide over the sum of DefectYesNo by the sum of SerialNumberCount .
Have a great day! 🙂
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @TcT85 ,
You can try this and let me know whether it works for you:
TomsFPYMeasure = VAR _helpTable = SUMMARIZE ( Table, Table[Serial number], "SerialNumberCount", DISTINCTCOUNT ( Table[Serial number] ), "DefectYesNo", CALCULATE ( COUNTROWS ( Table ), Table[Result] = "Defect" ) ) RETURN DIVIDE ( COUNTX ( _helpTable, [DefectYesNo] ), SUMX ( _helpTable, [SerialNumberCount] ) )
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi Tom,
Thanks for the formula, i got out 50% that I wanted.
But i encountered another issue with this formula when i added som more data.
Product C , i want that product to show 0% FPY instead of 100%.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |