Forum Discussion
Comparing dates to related table
Hey,
This is driving me nuts, and I would be able to do this in both SQL and Excel in minutes. I think I just dont understand the basics of DAX :-).
I have one parent table called Contacts and one child table called CallLog, like this:
Contacts: ID, Name, SalesPerson, AssignedToSalesPerson(DateTime)
CallLog: ID, ContactID, CallTime (DateTime)
I am trying to provide an aggregate overview that I can drill down on, comparing the hours between first Call time after it was assigned to a new SalesPerson on the Contacts table. So something like this in Excel: (MINIF(CallLog.CallTime, CallLog.CallTime > Contacts.AssignedToSalesPerson, Calllog.ContactID = Contacts.ID) - Contacts.AssignedToSalesPerson) on the Contacs table.
Any direction appreciated!
Hi Anonymous ,
You are right. It's a Row context which can't be propagated. Please try this one below.
FirstCallInterval = VAR firstCallDate = CALCULATE ( MIN ( 'CallLog'[CallTime] ), FILTER ( 'CallLog', 'CallLog'[CallTime] >= 'Contacts'[AssignedToSalesPerson] && CallLog[ContactID] = Contacts[ID] ) ) RETURN DATEDIFF ( [AssignedToSalesPerson], firstCallDate, HOUR )
Best Regards,
5 Replies
Hi Anonymous ,
Please download the demo from the attachment. You need a proper relationship.
Measure = DATEDIFF ( MIN ( Contacts[AssignedToSalesPerson] ), MIN ( CallLog[CallTime] ), HOUR )
Best Regards,
Thank you! I got that far actually, but sometimes we can have call logs that happened before the AssignedToSalesPerson date, so I need to get the MIN(CallLog[CallTime]) after AssignedToSalesPerson date.
For example here it would be CallLog ID 2 - 2019-02-15 17:47 (and not ID 1 2019-02-15 14:15). I would also want it to aggregate nicely, so I can look at the average of Hours per department and salesperson, so I assume it needs to be a Column?
Contacts
ID Name AssignedToSalesPerson 4 v-jiascu-msft 2019-02-15 16:31 CallLog
ID ContactID CallTime 1 4 2019-02-15 14:15 2 4 2019-02-15 17:47 3 4 2019-02-15 20:31 4 4 2019-02-15 21:43 Thanks so much for any more help!!
Hi Anonymous ,
Yes, a calculated column could make it clear. Below is the formula for a calculated column. If you have a large table, I would suggest you use a measure instead.
FirstCallInterval = VAR firstCallDate = CALCULATE ( MIN ( 'CallLog'[CallTime] ), FILTER ( 'CallLog', 'CallLog'[CallTime] >= 'Contacts'[AssignedToSalesPerson] ) ) RETURN DATEDIFF ( [AssignedToSalesPerson], firstCallDate, HOUR )
Best Regards,