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 an...
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,
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,
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