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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
150 | |
118 | |
111 | |
106 | |
95 |