Derived flag column in Table A which is evaluated from a lookup of other rows in Table A

[Anonymous]

I have a sales opportunity events table which contains customer events of various types, including incomming calls, emails ( welcome email), and other types of communications.  Its a "bucket of events" all which have a timestamp date.   These events are bundled together by a common CustomerID, and OpportunityID.  So, for Opportunity 123, there is a single customer "Acme", with 5 events:  4 calls, and 1 email.  3 of the calls came in before the email was sent, and 1 call came in after the email was sent

I have a requirement to count the number of event calls (Type=Call), in which the date of the call occurred after the welcome email (another event record int the same table) was sent out.  

I know I can set a CallAfterWelcomeEmailFlag or #DaysAfterWelcomeEmail in the physical database Call event row prior to loading into PowerBI.   Then this flag/duration is available to the data model, and can simply be counted/bucketized.

Alternatively, during load time, can this flag or #days be derived into the Call event record with PowerBI code ?  I would not want it to be calculated dynamically row by row, at run time in charts, for performance purposes.

Thanks,

Dave

[AlB]

Hi @Anonymous 

What do you mean exactly by during load time?

Do you want it as a calculated column in DAX or in Power Query.  Share some sample data plus the corresponding expected result.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.