AVERAGEX with RELATEDTABLE and 2 filters

[aafvincent]

I would like to compute the Readiness of a Product based on the average of the Readiness of the related Constituents. Products are related to Systems which in turn are related to Constituents.

Product Readiness = AVERAGEX(RELATEDTABLE(Constituent),[Constituent Readiness])

What I need to add to the formula above is 2 filters: 

1) Filtering out all Constituents which Constituents Status attribute is No longer applicable

2) Filtering out all Constituents which are related to Products through a Product/System relationship where RelationshipType attribute is Optional .

Please find the link to the Excel data and the PowerBI. 

Thank you for your support.

Antoine

[v-sshirivolu]

Hi @aafvincent ,
Thank you for reaching out to Microst Fabric Community Forum.

To accurately determine Product Readiness, calculate the average readiness of the associated Constituents, making sure to exclude any constituents marked as "No longer applicable" and those linked via an "Optional" relationship type.
Use the following Measure to achieve this result.

Product Readiness =
AVERAGEX(
FILTER(
RELATEDTABLE(Constituent),
Constituent[Constituent Status] <> "No longer applicable" &&
RELATED(System[RelationshipType]) <> "Optional"
),
Constituent[Constituent Readiness]
)

Explanation - 

FILTER: Limits the Constituent table to entries where Constituent Status is not "No longer applicable" and RelationshipType in the System table is not "Optional".

AVERAGEX: Calculates the average Constituent Readiness after these filters.

This measure now accurately reflects Product Readiness by including only valid Constituents.

Please find the attached .pbix file for your reference.
Thank you 

[johnt75 · Accepted solution]

RELATEDTABLE is actually syntax sugar for CALCULATETABLE with no additional filter arguments, which means that you can use CALCULATETABLE and add in the filters you require, e.g.

Product Readiness =
AVERAGEX (
    CALCULATETABLE (
        Constituent,
        Constituent[Constituents Status] <> "No longer applicable",
        'Relationship Product-System'[RelationshipType] <> "Optional"
    ),
    Constituent[Constituent Readiness]
)

[aafvincent]

Thank you for the clear answer.