Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
Hi, try this formula
Count of Jobs without Requests = COUNTROWS(Job) - DISTINCTCOUNT(Request[Job #])
Thanks- I'll try this one too!👍
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!!
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