Forum Discussion

Raj12's avatar
Raj12
Helper III
2 years ago
Solved

Calculated column based on ID match between two table and time comparison

Calculated column needs to created in transaction table1 if its Unique ID match with the call data table 2 unique ID and call was made within 10mins before the transaction time
Table1 : Transaction Table

Agent Transaction DateTimeUnique ID
A46-14/01/202210:40:00A46-14/01/2022-10:40:00
A16-15/01/202216:00:00A16-15/01/2022-16:00:00
A12-16/01/202214:00:00A16-16/01/2022-14:00:00

 

Table2 : Call Table

Agent Call DateTimeUnique IDValue
A46-14/01/202210:30:00A46-14/01/2022-10:20:00Pause
A46-14/01/202210:45:00A46-14/01/2022-10:20:00No Pause
A16-15/01/202216:02:00A16-15/01/2022-16:00:00Pause
A16-15/01/202216:03:00A16-15/01/2022-16:00:00Pause
A12-16/01/202213:58:00A16-16/01/2022-14:00:00Pause
A12-16/01/202214:02:00A16-16/01/2022-14:00:00No Pause

 

Output in Table1 : new calcualted column added in the table based on reason 

Agent Transaction DateTimeUnique IDoutputReason
A46-14/01/202210:40:00A46-14/01/2022-10:40:00Pause  As Transaction table1 has entry at 10:40:00 i.e. 10 mins after call time in table 2 hence Pause is okay
A16-15/01/202216:00:00A16-15/01/2022-16:00:00No Pause  As Transaction table1 has entry at 16:00:00 i.e. 2 mins before call time in table 2 so not considered
A12-16/01/202214:00:00A16-16/01/2022-14:00:00Pause  As Transaction table1 has entry at 14:00:00 i.e. 2 mins after call time in table 2 hence Pause

 

It would be really great if anyone can suggest on this.
Thanks

Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Write these calculated column formulas

    Pause entries within half an hour prior = CALCULATE(COUNTROWS(Agent),FILTER(Agent,Agent[Agent]=EARLIER('Transaction'[Agent])&&Agent[Transaction Date]=EARLIER('Transaction'[Transaction Date])&&Agent[Time]<=EARLIER('Transaction'[Time])&&Agent[Time]>=EARLIER('Transaction'[Time])-time(0,30,0)&&Agent[Value]="Pause"))
    Farthest time stamp in the agent table prior to the time stamp in this table = CALCULATE(max(Agent[Time]),FILTER(Agent,Agent[Agent]=EARLIER('Transaction'[Agent])&&Agent[Transaction Date]=EARLIER('Transaction'[Transaction Date])&&Agent[Time]<=EARLIER('Transaction'[Time])))
    Reuslt = if('Transaction'[Farthest time stamp in the agent table prior to the time stamp in this table]=BLANK(),"Missing",if('Transaction'[Pause entries within half an hour prior]=BLANK(),"No pause","Pause"))

     

16 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello Raj12,

     

    Can you please try this DAX :

    Output = 
VAR TransactionTime = Table1[Time]
VAR TransactionDate = Table1[Transaction Date]
VAR TransactionDateTime = TransactionDate + TIME(HOUR(TransactionTime), MINUTE(TransactionTime), SECOND(TransactionTime))
VAR UniqueID = Table1[Unique ID]

VAR CallDateTime = 
    CALCULATE(
        MAX(Table2[Call Date Time]),
        FILTER(
            Table2,
            Table2[Unique ID] = UniqueID &&
            DATEDIFF(Table2[Call Date Time], TransactionDateTime, MINUTE) >= -10 &&
            DATEDIFF(Table2[Call Date Time], TransactionDateTime, MINUTE) <= 0
        )
    )

VAR Reason = 
    IF(
        ISBLANK(CallDateTime),
        "No Pause",
        LOOKUPVALUE(Table2[Value], Table2[Unique ID], UniqueID, Table2[Call Date Time], CallDateTime)
    )

RETURN
Reason

    Should you require any questions, please do not hesitate to reach out to me.

    • Raj12's avatar
      Raj12
      Helper III
      2 years ago

      Hi Sahir,
      Thank you for your response.

      Can you please suggest how is this accounting for Table2: Call Table column Value Pause/No Pause?

      Sorry if I missed it initially but , Calculated column needs to created in transaction table1
      if its Unique ID match with the call data table 2 unique ID and
      call was made within 10mins before the transaction time , and
      output column should have respective mathcing Call Table column Value of Pause/No Pause

      Thank you for the help in advance

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Write these calculated column formulas in the Transaction table.

    Previous call = CALCULATE(MAX('Call'[Time]),FILTER('Call','Call'[Agent]=EARLIER('Transaction'[Agent])&&'Call'[Call Date]=EARLIER('Transaction'[Transaction date])&&'Call'[Time]<=EARLIER('Transaction'[Time])))
    Output = if('Transaction'[Time]-'Transaction'[Previous call]<=TIME(0,10,0),"Pause","No pause")

    Hope this helps.

     

    • Raj12's avatar
      Raj12
      Helper III
      2 years ago

      Hi Ashish,
      Thank you for your response.
      Can you please suggest how is this accounting for Table2: Call Table column Value Pause/No Pause?
      Sorry if I missed it initially but , Calculated column needs to created in transaction table1 if its Unique ID match with the call data table 2 unique ID and call was made within 10mins before the transaction time , and output column should have respective Call Table column Value