Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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,
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,
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
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |