Forum Discussion
Helper IExclude Project from evaluation if any version contains "Cancelled"/"Complete"
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
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.
1 Reply
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.