Count records in a joined table

[OleMcDonald]

I have two tables [Account] and [Contact]

They have a one (account) to many (contact) join 

The [Contact] table has a [Job Group] column

The [Job Group] is populated with a single value like "CEO" or "CFO" of "CIO"

I am trying to figure out how many [Account] records do not have a "CEO"

I tried using the Quick Measure to come up this this but it gives me a total count of accounts, not just accounts without  a CEO

Count of Id for CEO = 
CALCULATE(
	COUNTA('Account'[Id]),
	'Contact'[Job_Group__c] <> { "CEO" }

[edhans]

See if this measure works. You shouldn't use COUNTA() here. The only thing it can do that COUNT cannnot is use boolean comparisons, but I don't think you need that. Just count the rows that meet your critera.

Counting Measure =
COUNTROWS(
    FILTER(
        TableName,
        TableName[Field] <> "CEO"
    )
)

[AlB · Accepted solution]

Hi @OleMcDonald 

Count of Id for CEO =
COUNTROWS (
    FILTER (
        'Account',
        CALCULATE ( COUNT ( 'Contact'[Job_Group__c] ), 'Contact'[Job_Group__c] = "CEO" ) = 0
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers