How to count x2 distinct counts in 1 table (based on 2nd column criteria)

[Diane_Gribi]

Hi 

Looking for some help.  I am new to Power BI and I am trying to create a KPI calculation of Faults per unit

I have a table that records all of the test results (Pass and Fail).

I am looking to count the number of fails per MO number, count the number of distinct MO number (Both of these I can get), however, I want to only count the Mo's that have faults and then calculate the number of MO's that had not faults

(I will then  divide the faults by Total units (qty Mo's with fault +  Mo's without faults)

See attached table

Date	MO Number	Plant Number	Fault Code
01/02/2023	10000	Unit A	Inc Wiring
02/02/2023	10000	Unit A	Leak
03/02/2023	10001	Unit B	Pass
03/02/2023	10002	Unit C	Pass
04/02/2023	10004	Unit D	Inc Wiring

So it should say = 2 NFF (Pass), 3 faults from 2 units (dont mind them being separate columns that I then perform a calculation on)

When calculated it should calc (3/(2+2)) =0.75

What is the DAX formula (formulae) to achieve this

[andhiii079845]

You forget the define/copy the name: "Ratio =" 
Copy the whole defintion

Ratio = 
VAR _CountFail = COUNTAX(FILTER('Table','Table'[Fault Code]<>"Pass"),'Table'[Plant Number]) 
VAR _CountMO = COUNTROWS(DISTINCT('Table'[MO Number]))
RETURN _CountFail/_CountMO

[Diane_Gribi]

Thanks - Resolved it.  Had copied the first line but it read ration no ratio (Must have copied it wrong)

Now working

[andhiii079845]

Please mark my post as a solution if it suitable for you. 

[Diane_Gribi]

done - but it only partially solves my issue.  I am still looking to see if someone can help me witht he 2nd part.

NOTE - there is a typo in your original solution - it says ration, instead of ratio.

[andhiii079845]

Thank you. I corrected the typo. Do you try modify the measure for your own or what happened when you use it in the table with the product field? 

[Diane_Gribi]

Thanks I will try this. Love the idea of a single card presentation. Would the same formula work if I wanted to see this value for different products, could this data be presented in a table 

Product Name, Qty Faults, Qty units with Faults, Qty with No Faults, FPU

What would the formulas/measure look like to create and present these values

[andhiii079845]

FPU is the ratio we discuss earlier?

[Diane_Gribi]

Yes - but I was looking to see how I could split this by product and produce the columns above in a table (so that I can show the figures for analysis)

I currently have min of 3 product lines.  Can your ratio formula be filtered  using page or visual filtering 

Thanks for all of your support on this

[andhiii079845]

Perhaps create a example how the table should look like with the new. measures and i can try later to change the measure in the right way for you. 

Please use the table function in this forum, so i do not have to copy the datas manually from a screenshot 🙂 

[Diane_Gribi]

Date	InspectStage	MO	Plant Number	Product	Fault Code	Week Num	Month	Qtr	Year
01/01/01	Test	100111	ABC100	Product A	Pass	1	Jan	Q1	01
02/01/01	Test	100112	ABC101	Product A	Inc Wiring	1	Jan	Q1	01
02/01/01	Test	100112	ABC101	Product A	Fluid Leak	1	Jan	Q1	01
02/01/01	Test	100113	XYZ100	Product B	Pass	1	Jan	Q1	01
02/01/01	Test	100114	XYZ101	Product B	Fluid Leak	1	Jan	Q1	01
02/01/01	Test	100114	XYZ101	Product B	Inc Wiring	1	Jan	Q1	01
08/01/01	Test	100115	ABC102	Product A	Fluid Leak	2	Jan	Q1	01
08/01/01	Test	100115	ABC102	Product A	Fluid Leak	2	Jan	Q1	01
09/01/01	Test	100116	XZY102	Product B	Pass	2	Jan	Q1	01

Hi - Here is the table.  What I am looking to calculate is - 

By Week, Month, Qtr and Year

1) Qty of Fails (anything that does have pass in the fault code field)

2) Qty Units that have fails

3) Qty Unit with 0 faults (Any line that has pass in the fault code field)

4) FPU (No of fails/Total Qty Units (qty unit with fails + qty unit with 0 fails))

5) FPY (No of units with 0 fails/Total Qty Units (qty unit with fails + qty unit with 0 fails))

I have a reasonable handle on calculating 1, 2 (using the MO number with a distinct count) and 3.  But I am struggling to get it to calculate 4 and 5 as I cant get the DAX code or calculation to add a criteria into my distinct count to only count the MO's with a pass + MO's without stating pass

[Diane_Gribi]

Any Suggestions on the item above?

[andhiii079845]

You are welcome. Than please give a full data example. It will be possible to create a the requiered measures. 

[Diane_Gribi]

Date	MO Number	Product	Plant Number	Fault Code
01/02/2023	10000	A	ABC001	Inc Wiring
01/02/2023	10000	A	ABC001	Leak
01/02/2023	10001	A	ABC002	Pass
02/02/2023	10002	B	XYZ001	Assy Fault
02/02/2023	10004	B	XYZ002	Pass

Here is some data

[andhiii079845 · Accepted solution]

Try this:

Ratio = 
VAR _CountFail = COUNTAX(FILTER('Table','Table'[Fault Code]<>"Pass"),'Table'[Plant Number]) 
VAR _CountMO = COUNTROWS(DISTINCT('Table'[MO Number]))
RETURN _CountFail/_CountMO

[Diane_Gribi]

Hi - Sorry, I copied the formula above but it is returned an error around the "return" syntax.  Any advice?