Count of Positions without a request

[naninamu]

Hi,

I feel this should be simple, but can't seem to get it. 

I have 2 tables: one is a list of Job #, the other a list of Requests with a related Job #.

Job #
1
2
3
4
5
6
7
8

Request	Job #
A	2
B	4
C	5

I'm simply trying to get a count of how many Job # do not have a Request against them.

So there are 3 Requests againt Job # 2, 4 and 5. Therefore Jobs 1, 3, 6, 7, 8 do not have Requests against them so the answer = 5.

I could do this with a countif in Excel, is there a straightforward way in Power BI?

Many thanks, Andrew

[expertopinionsa]

Hi, try this formula 

Count of Jobs without Requests = COUNTROWS(Job) - DISTINCTCOUNT(Request[Job #])

[naninamu]

Thanks- I'll try this one too!👍

[naninamu]

Thanks! I'll give it a go when I get home. Just reading that though - is that not going to return the number of Jobs WITH a request? Ie where there's a matching ID? I want the opposite..

[naninamu]

Sorry! Read it too quickly, I see what you're doing ! 😁thanks!!

[krishb1414]

Hi @naninamu  try this measure 

VAR_job = ReturnJob[id]
RETURN
COUNTROWS(
    FILTER(
        ALL(Job),
        _Job = Job[id]
    )
)

and don't forget to create relationship b/w these two tables.

Thanks