Exclude Project from evaluation if any version contains "Cancelled"/"Complete"

[DataUser]

I've got a file of projects containing current and past information - multiple entries for each project. If any of those versions show up as Status "Cancelled" or "Complete" then I don't want to evaluate that project in a subsequent measure. When I try && Table[Status]<> "Cancelled"  in my measures, it only removes the record that shows as cancelled, but any other records are included in my evaluations. 

1st - I don't want to further evaluate any record where a version of the record has "Complete" or "Cancelled".

2nd - Ultimately, I want to return the most recent date for those projects, including if it's blank.  Any assistance would be awesome. Thanks

[v-yetao1-msft · Accepted solution]

Hi @DataUser 

Please correct me if I wrongly understood your question.

(1)Create a measure to filter the Project that status <>” Complete” and “Cancelled” .

Measure1 =

var _all=SUMMARIZE(FILTER(ALL('Data'),'Data'[Status]="Complete" || 'Data'[Status]="Cancelled"),[Project])

return IF(MAX('Data'[Project]) in _all,1,0)

Then put the measure in filter ,set the value is 0 to return the Project that status <>” Complete” and “Cancelled” .

(2)Create  measures to filter the Project that date is max .

Max date = CALCULATE(MAX(Data[Attestation Date]),ALLEXCEPT(Data,Data[Project]))

Measure 2 = IF(SELECTEDVALUE(Data[Attestation Date])=[Max date],1,0)

Then put the measure in filter ,set the value is 1 to return the Project that date is max .

The effect is as shown

I have attached my pbix file, you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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