HELP Required :: Related Tables DAX Query :: New column based on Text

Hello Power BI champions

I have two Related tables Table A to Table B ( 1 to many )

Table A - With Product IDs ( Unique ) and one Entry per Product  ID 

Table B - With multiple entries of the same Product IDs with their related part Numbers and at part Level compliance YES or NON-C

Based on that data  

I want to have one column against each unique Product ID in Table- A if any of the active parts are NON-C for that Product ID in Table B, then the whole product will become Not  Compliance in Table A

Table A 

Table B 

Table C:: Desired Output

Thanks