Forum Discussion
RELATED function with many:1 relationship
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?