Forum Discussion
Resolver ICount 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.
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
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])
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])
2 Replies
- amitchandak
Super User
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
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])