Comparing dates to related table

[Anonymous]

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!

[v-jiascu-msft]

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,

[Anonymous]

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!!

[v-jiascu-msft]

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,

[Anonymous]

Hey,

Don't I need a relationship in the filter as well? It looks like I need it, even though I have a proper relationship in the model between Contacts and CallLog. I think this is what confuses me. If I do not have it, it seems like its comparing AssignedToSalesPerson with all CallTimes, even though the relationship is there in the Model.

Is this the correct way of doing it? 

   FILTER ( 'CallLog', 'CallLog'[CallTime] >= 'Contacts'[AssignedToSalesPerson] ) <-- doesn't work

, 'CallLog'[ContactID] = 'Contacts'[ID]  <-- adding this makes it work

[v-jiascu-msft · Accepted solution]

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,