Forum Discussion
Voicemails returned
calculated column:
VM Ret = if([Phone Results]="Voice Mail", var c = [Contact] var cs = [Case Subject] var sd = [Sent Date] var r = calculate(COUNTROWS('Table'),ALL('Table'),'Table'[Contact]=c,'Table'[Case Subject]=cs,'Table'[Message Type]="Incoming",'Table'[Sent Date]>sd) return if(r>0,"True","False"))see attached.
Hi,
Line 6 (because it has phone result of "voicemail") would have an additional column indicating (true/false) that at a later sent date an incoming message type from the same student (contact) with the same case subject (campaign name) was logged. Line 7 would be an example of false as although there are later incoming message types, none from the specific case subject (2022 May DOHS Information Session Follow-up).
My data set would have many different contacts and case subjects to analyze. Really appreciate your insight and if you have any suggestions/ideas.
Added the result below for voicemail returned column and the row that would yield a positive/negative result.
Thanks
| Contact | Message Type | Channel | Phone Results | Case Message Subject | Case Subject | Sent Date | Voicemail Returned? |
| Kate Gowen | Incoming | DOHS discount payment | Gowen DOHS | 16/08/2022 08:14 AM | |||
| Kate Gowen | Outgoing | DOHS discount payment reply | Gowen DOHS | 16/08/2022 08:14 AM | |||
| Kate Gowen | Outgoing | acceptance letter | Gowen DOHS | 15/08/2022 04:03 PM | |||
| Kate Gowen | Outgoing | Registration reply | 2022 OHS/CPSL 30% Discount Non-Consent Follow-UP | 15/08/2022 03:23 PM | |||
| Kate Gowen | Incoming | Ready to register | 2022 OHS/CPSL 30% Discount Non-Consent Follow-UP | 15/08/2022 03:18 PM | |||
| Kate Gowen | Outgoing | Phone | Voice Mail | Left Voicemail. No Consent to Email | 2022 OHS/CPSL 30% Discount Non-Consent Follow-UP | 02/08/2022 02:57 PM | True |
| Kate Gowen | Outgoing | Phone | Voice Mail | Left Voicemail. No Consent to Email | 2022 May DOHS Information Session Follow-up | 20/07/2022 03:35 PM | False |
calculated column:
VM Ret =
if([Phone Results]="Voice Mail",
var c = [Contact]
var cs = [Case Subject]
var sd = [Sent Date]
var r = calculate(COUNTROWS('Table'),ALL('Table'),'Table'[Contact]=c,'Table'[Case Subject]=cs,'Table'[Message Type]="Incoming",'Table'[Sent Date]>sd)
return if(r>0,"True","False"))see attached.