Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |