Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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

2 REPLIES 2
Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.