topic Re: AVERAGEX with RELATEDTABLE and 2 filters in DAX Commands and Tips

AVERAGEX with RELATEDTABLE and 2 filters

aafvincent — Wed, 30 Jul 2025 08:21:53 GMT

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

Re: AVERAGEX with RELATEDTABLE and 2 filters

johnt75 — Wed, 30 Jul 2025 08:50:10 GMT

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] )

Re: AVERAGEX with RELATEDTABLE and 2 filters

aafvincent — Wed, 30 Jul 2025 09:05:50 GMT

Thank you for the clear answer.

Re: AVERAGEX with RELATEDTABLE and 2 filters

Anonymous — Wed, 30 Jul 2025 09:34:34 GMT

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