Count Records Without Match in Related Table

[JSiebrecht]

I have 2 related tables:
'Contract' with a looooong list of contracts conducted
'Contract_UdF' a table accounting for some user defined fields that could not be added to the general data structure.

Records in the UdF table are linked by the foreign key [CONTRACT_ID].
Particular items are identified by [NAME] (e.g. "Inst Type"). [VALUE] then contains the actual data.

I need a measure with the overall COUNT of records in 'Contract' that do NOT have a corresponding record in 'Contract_Udf'

with [NAME] = "Inst Type".

Example:

I have 4 contracts in the 'Contract' table. They have corresponding records in the related table 'Contract_UdF'. 
[CONTRACT_ID] 100000001, 100000003 and 100000004 have a record wiht [NAME] = "Inst Type".
But 100000002 does NOT.

The value of the measure shall be 1.

THANKS FOR YOU HELP!

Best regards,
Jan

[amitchandak · Accepted solution]

@JSiebrecht , Try a measure like

countx(filter(summarize(Contract_udf, contract[contract], "_1", countrows(filter(Contract_udf, Contract_udf[Name] = "Inst Type"))+0), [_1]=0), [contract])

[amitchandak · Accepted solution]

@JSiebrecht , Try a measure like

countx(filter(summarize(Contract_udf, contract[contract], "_1", countrows(filter(Contract_udf, Contract_udf[Name] = "Inst Type"))+0), [_1]=0), [contract])

[JSiebrecht · Accepted solution]

Hi @amitchandak 
thanks for the prompt response.

With the small typological adjustment (in pink below) it works fine! Many thanks.

Checking the results maually I found a glitch in my thinking though.
How do I need to adjust the measure if Inot only want to count the non-existing records
but also the existing records with [NAME] = "Inst Type" but a blank [VALUE]?I 

I suppose simply add the additional filter (in green) like so, isn't it?!?

CountContracts_NULL_Inst_Type =

countx(filter(summarize('Contract Udf', 'Contract Udf'[CONTRACT_ID], "_1", countrows(filter('Contract Udf', 'Contract Udf'[NAME] = "Inst Type" && 'Contract Udf'[VALUE] <> BLANK()))+0), [_1]=0), [CONTRACT_ID])