Forum Discussion
A more complex question about the RELATED() statement for 1..* relations
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).
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 */
If it helps: I do have the option to manipulate the source tables in the database (as they are views).