Set Analysis not returning the desired results

[wcoleman0908]

I've been unsuccessful in getting this stuburn code to work correctly. What I'm attempting is to return is all records where:

1) The [project status] is open

2) Return those projects where NO projects have any values with a  [completion status] equal to 'In_Prog'

The problem is that it returns projects but includes those projects with 'In_Prog' data values. My task is to Complete those projects that are open that are no longer 'In_Prog'

My set Analysis is as follows:

Count(aggr(Count({<[Project Status]={'Open'},[Completion Status]-= {'In Progress'}   > }distinct [Projects]), [Projects))

I included a sample data file for testing, Any insights would be great

[BrunPierre · Accepted solution]

Hi, perhaps this.

=Count({$<[Project Status]={'Open'}, [Projects] -= P({<[Completion Status]={'In_Prog'}>} [Projects])>} DISTINCT [Projects])

[wcoleman0908]

No that returned In prog project records too. It needs to return a list of projects that have NO 'in_prog' records.

😞

[BrunPierre]

 isn't this what you want?

[coleturegroup]

Here is what i'm seeing locally still getting returned

[coleturegroup]

See here:

[BrunPierre · Accepted solution]

As a filter pane or calculated dimension use this

=Aggr(Only({$<[Project Status]={'Open'}, [Projects] -= P({<[Completion Status]={'In_Prog'}>} [Projects])>} [Completion Status]), [Completion Status])

[coleturegroup]

Holly cow this is very painful, It still shows, look at project 8892

[BrunPierre]

Add the count measure to filter the result.

[MayilVahanan]

Hi

One of the way to achieve this ,

Count({<[Project Status]={'Open'}, Projects-={$(=Concat({<[Completion Status]={'In_Prog'}>}Distinct Projects, ','))}>}DISTINCT Projects)