need DAX to emulate Subselect to find parent records without child records (self-referencing)

[Anonymous]

The requirement is to find records that have no children...the application in this case is to find incoming emails (emailid) in CRM that have not been replied to...in other words, where *the emialid does not appear in any other record's parentactivityid field* in the same table. 

In SQL the query would look something like..

select emailid from emails e
where
   (select count(parentactivityid)
   from emails p
   where e.emailid = p.parentactivityid and e.createdon<p.createdon
   )
< 1

To be clear, email responses list the original emailid in the parentactivityid field. Therefore, emails that have not been responded to will not have their emailid entered in any parentactivityid record, therefore the subselect looks for count = 0.

Please excuse the long explanation.

any help is appreciated

[Greg_Deckler]

Probably something along the lines of:

Measure 2 = 
VAR __table = ALL('Table4')
VAR __table1 = ADDCOLUMNS(__table,"__count",COUNTX(FILTER(__table,[parentactivityid]=EARLIER(Table4[emailid]) && [createdon]>EARLIER([createdon])),[emailid]))
VAR __table2 = FILTER(__table1,[__count]<1)
RETURN
CONCATENATEX(__table2,[emailid],",")

See Page 3, Table 4.

[Anonymous]

I'll be working your through suggestion, along with Microsoft's suggestion to use PATH (to determine parent-child relationships), along with a web example I found showing a similar application using the CALCULATETABLE expression. 

I'll post findings as soon as I get to the bottom of this. Thx for your suggestions.