Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

IDNameAssignedToSalesPerson
4v-jiascu-msft2019-02-15 16:31

 

CallLog

IDContactIDCallTime
142019-02-15 14:15
242019-02-15 17:47
342019-02-15 20:31
442019-02-15 21:43

 

Thanks so much for any more help!!

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      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,