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

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
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"))

 

Raj12's avatar
Raj12
Helper III
2 years ago

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