RELATED function with many:1 relationship

[illmatic]

I know this has been asked a lot, but I still can't seem to get pointed in the right direction.  I am trying to utilize the RELATED function in DAX to create a calculated column based on the values in a table visualization.   I have three tables that store different data:  [Projects for Schedule Health], [Risks for an average Risk Score per project], and [Artifacts for a sum of artifact score per project].   I need to take each of these values, and utilize them in a calculated column.

I cannot use the 'RELATED' function because the relationships to my Projects table [which has the unique identifer] are all many:1 as demonstrated in the picture.   When attempting to invert this relationship, it simply defaults backto many:1.  Joining the tables together results in duplicate rows that I don't need.

power bi relationships

What would be the best method to yield the calculated column?

Thank you in advance for any guidance!

[illmatic]

I wanted to note that I think I have been able to do what I need, but I'm not sure it is "sound."  I kept the relationships as is, and instead went to the Risks field and added a Measure.  The Measure formula is as follows:

Project Health = 10 *(10- (AVERAGE(Risks[Risk Score]))) *.3 + CALCULATE(SUM(Artifacts[Artifact Score]) *.2) + CALCULATE( MIN( Projects[Schedule Health])) *.5

Schedule Health is only displayed once in Projects table, but renders multiple times when pulled into Risks because ProjectId occurs multiple times.  Therefore, as I expect this number to be the same each time it displays in a row, I just used MIN to grab it. 

Measure formula meets my needs, but as this is my first time doing something pretty complex with PowerBI, would anyone mind reviewing?   My Case Scenario is as follows:

- I am creating a dashboard for Project data and querying to Project Online via an OData feed.  I have a custom SP List which I also have a query to in order to get some fields from there.  I've been able to relate all of these tables to my Projects table which has unique identifiers via the ProjectId and ProjectName.

- The end goal is to have three fields that contain metrics about each Project:  Risk Score [average], Artifact Score [average], and Project Health [queried from PercentComplete in Projects Table].   Then take each of these scores and calculate them based on a formula.   Each project should only show up once in my table visualization.

Is there a better way to achieve the above?

[Anonymous]

You should be able to bring the calculated columns into the Project table fairly painlessly for example if you wanted the Average risk Score, create a calculated column in the projects table and input: 

Average risk score = CALCULATE(AVERAGE('Risks'[Risk Score]),'Risks'[ProjectId])

[illmatic]

Adding the filter gives me an error, but the first portion of this works.  I'm assuming you are filtering based on the Project ID so it only averages for that specific projectID, however, this throws the error:  "cannot convert value of [ProjectID] of type text to true/false."

[illmatic]

I'll try this route as well.  I think I was trying to use the RELATED function to bring in a measure from the Risks table and that wasn't working.  Thanks for providing an alternate resolution!  I think this one may be much simpler and more direct.