DAX Help Needed: Asset Numbers that Appear Only Once Under Multiple Filter Conditions

Hi MSargeant ,

JamesFR06  said it very well, I have another method here, I hope it will be helpful to you.

1. Create a calculation table and filter out that WORK DESCRIPTION is Examination - Revisit, and COMPLETED? is YES.

FilteredAssets =
FILTER(
SUMMARIZE(
'Table',
'Table'[ASSET NUMBER],
"WorkDesc", MAX('Table'[WORK DESCRIPTION]),
"Completed", MAX('Table'[COMPLETED?])
),
[WorkDesc] = "Examination - Revisit" && [Completed] = "YES"
)

2. Create a measure to count the fields of the compound condition.

AssetCount =
CALCULATE(
DISTINCTCOUNT('Table'[ASSET NUMBER]),
FILTER(
ALL('Table'),
'Table'[SEASON] = MAX('Table'[SEASON])
&& 'Table'[RATING] = MAX('Table'[RATING])
&& 'Table'[WORK DESCRIPTION] IN {"Examination", "Examination - Revisit"}
&& 'Table'[COMPLETED?] = "NO"
&& 'Table'[STATUS] = "COMPLETE"
&& NOT(
'Table'[ASSET NUMBER] IN VALUES(FilteredAssets[ASSET NUMBER])
)
)
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.