Create conditional column based on Two tables

[graceweii]

I have two tables: Tests and Results. In the data model, the two tables are connected by the key column and it is a one (Tests) to many (Results).  relationship. 

Tests: 

key	Lot code
1	xxx
2	yyy
3	zzz

Results : 

key	test	condition
1	A	pass
1	B	pass
2	A	fail
2	D	pass
2	C	pass
3	B	fail
3	D	fail

I want to produce the following table without merging the two tables:

Lot code	Final Condition
xxx	pass
yyy	fail
zzz	fail

The final table is produced by first getting the lot code and condition columns from the two tables. Then, If the lot code has any condition = fail, then the final condition of the lot will fail. In other words, for a lot to pass, the lot can only have pass conditions. 

Lot code	condition	final condition
xxx	pass	pass
xxx	pass	pass
yyy	fail	fail
yyy	pass	fail
yyy	pass	fail
zzz	fail	fail
zzz	fail	fail

I'm thinking of creating a new measure or new column on Power BI Desktop. But totally clueless how to write this. I hope I explained everything clearly. Please let me know if I need to clarify anything! 

Thank you in advance! 

[ryan_mayu · Accepted solution]

@graceweii 

is this what you want?

Measure = if(COUNTROWS('Results')=CALCULATE(COUNTROWS(Results),FILTER(Results,Results[condition]="pass")),"pass","fail")