Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
naninamu
Helper IV
Helper IV

Count of Positions without a request

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

 

RequestJob #
A2
B4
C5

 

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

5 REPLIES 5
expertopinionsa
Regular Visitor

Hi, try this formula 

 

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

 

Thanks- I'll try this one too!👍

naninamu
Helper IV
Helper IV

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..

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

krishb1414
Helper III
Helper III

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

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors