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

[Raj12]

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 Date	Time	Unique ID
A46-14/01/2022	10:40:00	A46-14/01/2022-10:40:00
A16-15/01/2022	16:00:00	A16-15/01/2022-16:00:00
A12-16/01/2022	14:00:00	A16-16/01/2022-14:00:00

Table2 : Call Table

Agent Call Date	Time	Unique ID	Value
A46-14/01/2022	10:30:00	A46-14/01/2022-10:20:00	Pause
A46-14/01/2022	10:45:00	A46-14/01/2022-10:20:00	No Pause
A16-15/01/2022	16:02:00	A16-15/01/2022-16:00:00	Pause
A16-15/01/2022	16:03:00	A16-15/01/2022-16:00:00	Pause
A12-16/01/2022	13:58:00	A16-16/01/2022-14:00:00	Pause
A12-16/01/2022	14:02:00	A16-16/01/2022-14:00:00	No Pause

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

Agent Transaction Date	Time	Unique ID	output	Reason
A46-14/01/2022	10:40:00	A46-14/01/2022-10:40:00	Pause	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/2022	16:00:00	A16-15/01/2022-16:00:00	No 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/2022	14:00:00	A16-16/01/2022-14:00:00	Pause	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

[Ashish_Mathur]

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]

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 

[Ashish_Mathur]

What happens when you use my formula?  Is my result incorrect?

[Raj12]

Hi Ashish,

Final result should be driven by table 2 Value column i.e. if all matching conditions are met then output sould show Table 2 Value column respective data of Pause/No Pause instaed of below logic which shows if condition is met its "Pause" , if not its "No Pause"

Output = if('Transaction'[Time]-'Transaction'[Previous call]<=TIME(0,10,0),"Pause","No pause")

[Ashish_Mathur]

Share the download link of the PBI file with my calculated column formulas already written there.

[Raj12]

Sorry, I can't see any option in there to add an pbix file.

[Ashish_Mathur]

Try this calculated column formula

Output = if(ISBLANK('Transaction'[Previous call]),"No pause",CALCULATE(MAX('Call'[Value]),FILTER('Call','Call'[Agent]=EARLIER('Transaction'[Agent])&&'Call'[Call Date]=EARLIER('Transaction'[Transaction date])&&'Call'[Time]=EARLIER('Transaction'[Previous call]))))

Hope this helps.

[Raj12]

Hi Ashish,

Thank you for the response.
Its working fine in comparing last call from Call table with transaction table.
If comparsion is needed for any call made (Call table) is within 30 mins before the transaction time (Transaction Table) and then return OUTPUT if call table has any Pause (within last 30 mins) , if not then if it has No Pause  

[Ashish_Mathur]

Hi,

You have completely changed the requirement.  For this new one, share some data to work with and show the expected result clearly.

[Raj12]

Hi Ashish,
Thank you so much for the help.
Logic:  If call made (Call table) is within 30 mins before the matching transaction time (Transaction Table) and then return OUTPUT column if call table has any Pause (within last 30 mins) , if not then if it has only No Pause then output No Pause, else no matching calls in last 30 mins then Missing
Please find below 

Data :

Table1 : Transaction Table
Agent Transaction Date	Time	Unique ID
A46-14/01/2022	10:40:00	A46-14/01/2022-10:40:00
A16-15/01/2022	16:00:00	A16-15/01/2022-16:00:00
A12-16/01/2022	14:00:00	A16-16/01/2022-14:00:00
Table2 : Call Table
Agent Call Date	Time	Unique ID	Value
A46-14/01/2022	10:30:00	A46-14/01/2022-10:20:00	Pause
A46-14/01/2022	10:45:00	A46-14/01/2022-10:20:00	No Pause
A46-14/01/2022	10:28:00	A46-14/01/2022-10:20:00	No Pause
A16-15/01/2022	15:58:00	A16-15/01/2022-16:00:00	No Pause
A16-15/01/2022	15:28:00	A16-15/01/2022-16:00:00	No Pause
A16-15/01/2022	15:38:00	A16-15/01/2022-16:00:00	No Pause
A12-16/01/2022	14:01:00	A16-16/01/2022-14:00:00	Pause
A12-16/01/2022	14:02:00	A16-16/01/2022-14:00:00	No Pause
New Column in Transaction Table
Agent Transaction Date	Time	Unique ID	output	Reason
A46-14/01/2022	10:40:00	A46-14/01/2022-10:40:00	Pause	As Transaction table1 has entry at 10:40:00 and 30 mins before that call table has one entry of Pause hence output Pause
A16-15/01/2022	16:00:00	A16-15/01/2022-16:00:00	No Pause	As Transaction table1 has entry at 16:00:00 and 30 mins before that call table has only entry of entry of No Pause hence o/p No Pause
A12-16/01/2022	14:00:00	A16-16/01/2022-14:00:00	Missing	As Transaction table1 has entry at 14:00:00 i.e. no call before that time in table 2 hence output is Missing

[Ashish_Mathur · Accepted solution]

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"))

[Raj12]

Thannk you so much for you help and providing solution for this

[Raj12]

Hi Ashish, Whenever possible can you please suggest on the above/

Thank you

[Ashish_Mathur]

Hi,

As requested, share the download link.  Upload the file to Google Drive or a similar file hosting service and share the download link.

[Sahir_Maharaj]

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]

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