Forum Discussion

JSiebrecht's avatar
JSiebrecht
Icon for Resolver I rankResolver I
4 years ago
Solved

Count Records Without Match in Related Table

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....
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
JSiebrecht
Icon for Resolver I rankResolver I
4 years ago

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])