Returning values only where count >=1 in a list

[orangebloss]

I'm trying to return a list in a pivot table that shows only values that meet 2 specific criteria :

1. Lever = NPD

2. The count of 'Due' in the field 'Status' is greater than 1

if( Lever='NPD',Project) returns all the projects with NPD in the Lever field, however what I want is for only projects that have tasks with 'Due' in the status field and NPD in the Lever field.

I've tried 

if(Lever='NPD' and (count(Status,'Due')>=1),Project)

But it's returning an error - I feel like this should be an easy one but I've not had enough coffee!

[igoralcantara]

There are a few ways to solve this problem. 

My favorite one is to create a flag field in your script. Something like:

StatusCount:
LOAD
Status,
Sum(If(Status = 'Due', 1, 0)) as DueFlag
Resident myTable
GroupBy Status
;

Then, simply use DueFlag={">=1"} in your set analysis

[BrunPierre]

Try

=Count(Distinct{$<Lever={'NPD'},Status={"=Count({1<Status={'Due'}>}Status)>=1"}>}Project)

[rwunderlich]

If you are trying to create Dimension, try 

Aggr(Only($<Lever={'NPD'},Status={"=Count({1<Status={'Due'}>}Status)>=1"}>} Project), Project)

-Rob