Count rows on the One side checking for condition on Many side of One-To-Many Relationship

[RYU209]

Hello,

I have a one-to-many relationship with the One side being an imported table (unique Transactions) and the Many side (Attachments on Transactions) coming from a direct query. Their relationship is linked on the Transaction ID. Each Transaction can have multiple attachments and this is denoted on the Many side as an Attachment column that is 1 if the attachment exists and 0 if not. Also, not every record that exists on the One side will be present on the Many side and there will be transactions on the Many side that do not exist in the One side as well. 

One Side (Unique Transactions)

Transaction
A
B
C
D
E

Many Side (Attachments on Transactions)

Transaction         Attachment
A                         1
A                         0
B                         1
B                         1
C                         0
C                         0
X                         1
Y                         1

How can I create a measure that counts the amount of rows in the one side that does not have an Attachment sum of 2 or more? Keep in mind this model uses Direct Query on the many side. 

[RYU209]

Hi @tamerj1 ,

I'm getting the error message "The column 'Transaction' specified in the SUMMARIZED function was not found in the input table". 

[tamerj1]

@RYU209 

Can please share a screenshot of the code you've tried?

[RYU209]

@tamerj1 

[tamerj1]

@RYU209 

Attachment is on the many side and Transactions is on the one side of a direct relationship between both tables. Is that correct?

[RYU209]

@tamerj1 Correct

[tamerj1 · Accepted solution]

@RYU209 

Strange! However, you may try

=
COUNTROWS (
FILTER (
SUMMARIZE (
Attachments,
Attachments[Transaction],
"@Attachments", SUM ( Attachments[Attachment] )
),
[@Attachments] < 2
&& Attachments[Transaction] IN VALUES ( Transactions[Transaction] )
)
)

[tamerj1]

Hi @RYU209 

Please try

=
COUNTROWS (
FILTER (
SUMMARIZE (
Attachments,
Transactions[Transaction],
"@Attachments", SUM ( Attachments[Attachment] )
),
[@Attachments] < 2
&& Transactions[Transaction] <> BLANK ()
)
)