Need to count the Processes that have exclusively one type of status.

[Hemu]

Hello,

i am trying to develop a code to find the count of process where the status is exclusively 'not in scope' and does not have any other status for a particular Unit. The source data is attached.

i tried with this code:

tier_3:
Load

Major category,
unit,
'Tier3' As Tool,
Count(DISTINCT (if([GFC Status]='Not in Scope',Process))) as Process

From sourcedata
Group by Major category,Unit, Process;

But this is giving me all the distinct count of Process against the units with 'not in scope' status.

for eg in the shared data i have unit 2200 with only one process i.e., seven with only and only 'not in scope status, whereas other processes have a combination of NA, in-scope, not in scope and partially in scope.

Just to add all units have 7 distinct process(one,two,three.....,seven). Also i need the balance process into other measure as in scope.