Awkward count in expression

[#1]

I need to perform the following count:

I have 2 tables, Work Requests and EngineerAssignments, these are joined via the work request code field.

I need to know how many work request have an engineer assigned and how many are unassigned (work request may have more than one engineer assigned!)

I know I have 3209 work requests (I can work this out in a text object quiet easlily).

I can't count the number of records in the EngineerAssigments table as its doesn't give a unique count against work request number.

I have 1512 records in the EngineerAssigments table, but as some have multiple engineers are assigned to a single work request this is not the number I need.

The total row in the EngineerAssigments straight table shows 899 - so I assume this is 899 work request which have one or more engineers assigned.  this is the value I need to produce in a text object or even to show a pie chart of assigned and unassigned work requests.

for example:

Work Request Table:

Work request Code
001
002
003
004
005
006
007

EngineersAssigned Table:

Work Request Code	Engineer
001	A
001	B
002	A
004	B
004	C
006	A

So here I know there are 7 work requests.  And although there are 6 entries in the EngineersAssigned table, there are only 4 work requests which have someone assigned.

So the result I'm looking for would be 4 Assigned Work Requests and 3 Unassigned Work Requests.

Any ideas on the expression i need to count the assigned and unassigned work requests?

[Gysbert_Wassenaar · Accepted solution]

Assigned: count({<Engineer={'*'}>} distinct [Work Request Code])

Unassigned: count({$-<Engineer={'*'}>} distinct [Work Request Code])

[#3]

Thanks Gysbert work perfectly