A more complex question about the RELATED() statement for 1..* relations

[CoTheiss]

The background:

My PowerBI Report is about displaying another system's data quality (as the system is mainly filled manually).

So if you had a table that is called e.g. Orders, which has the fields "order reference" and "order date", these fields are supposed to be filled properly according to a defined scheme. 

But as people are sometimes lazy or tend to enter wrong stuff, we have this PowerBI report to indicate any data quality issues.

Meaning: you get points for each field that has data entered according to the scheme.

The issue:

The calculation itself works fine per table and also across other tables - except one relation, where the main issue here comes from the table relation of the two tables itself (see screenshot below).

An "order" can have details - and in this case 0..* detail lines - while there is only one active at a time.

No matter if there are detail lines or not - I need to show the total score per order 

(because actually, everything should have at least one detail line, and it is already a data quality issue if there is no line !)

Due to this relation I have the following issues:
option 1) if I add the column for Total Score into the Orders table, PowerBI won't allow me to select any columns from the OrderDetails table in the related Statement ("cannot find fieldname")

TotalScoreInOrders =

VAR Score3= RELATED(OrderDetails[Score3]) /* this line does not work due to the relation */
Return Orders[Score1] + Orders[Score2] + Score3

option 2) if I add the column for Total Score into the OrderDetails table, and handle the ocassions when there are multiple lines (either by setting the score to 0 or by filtering on Active=TRUE), I get results for Orders with OrderDetails (independent of the #ofDetailLines). 

--> but: it will show (blank) instead of the Totif the Order has no OrderDetails - which makes sense, because if there is no line, there is nothing to show. 

TotalScoreInOrderDetails = 
VAR Score1= RELATED(OrderDetails[Score1])
VAR Score2= RELATED(OrderDetails[Score2])
Return Score1 + Score2 + OrderDetails[Score3]

/* this column works, but will post no result (blank) if there are no detail lines */

My question now is:

how can I achieve that the OrderDetails-fields are selectable in the Related statement of option 1) ? 

Because I do not think that I can fake lines to make option 2) run 🙂 

If it helps: I do have the option to manipulate the source tables in the database (as they are views).

P.S: i needed to rename the tables - so it is not really about orders and order details, but that's the closest similar relation I could find.

Any helps, ideas or "loud thinking" appreciated ! 

[parry2k]

@CoTheiss not sure why you are using related functions. How does your data model look like, especially with the table used in your example? Are you adding measures?