New column calculating between two tables

[Anonymous]

Morning,

I am having an issue, hope someone could find a solution and help. Will do my best to make it clear !

TableA: Interaction

In this table I have sales interaction with client, when and what did they talk about. That look like this

DATE	INTERACTION_ID	CLIENT	TYPE	SUBJECT
01/01/2023	XXX1	CLIENT_1	Email	A
01/01/2023	XXX1	CLIENT_1	Email	B
01/01/2023	XXX2	CLIENT_2	Email	B
01/01/2023	XXX3	CLIENT_2	Call	A

As you can see, one INTERACTION_ID can be about multiple subjects. 

TableB: Transaction

In this table I have transactionm that look like this

DATE	TRADE_ID	CLIENT	SUBJECT	REVENUE
01/01/2023	YYY1	CLIENT_1	A	10
05/01/2023	YYY2	CLIENT_2	B	10
10/02/2023	YYY3	CLIENT_3	D	10
08/01/2023	YYY4	CLIENT_3	A	10

I also have 3 tables that contains unique values:

- one that is a list of subjects

- one that is a list of clients

- one is a calendar

The two tables above are connected to those three.

What I would like to do is to measure for each client their "sensitivity" to sales pitch.

My idea was add a column in the table A and put a 1 if a trade occurs for same client, on same subject and between interaction date and interaction date +15days. Otherwise 0. Then create a measure to calculate %.

Unfornatly the below is working with the data given above but is not working with my real data because it is taking ages to calculate/crash:

HAS_TRADED_15D =CALCULATE(
DISTINCTCOUNT(TABLE_B[TRADE_ID]),
FILTER(
TABLE_B, TABLE_B[TRADE_DATE] >= TABLE_A[INTERACTION_DATE]),
FILTER(
TABLE_B, TABLE_B[TRADE_DATE] <= TABLE_A[INTERACTION_DATE]+15),
FILTER(
TABLE_B, TABLE_B[CLIENT] = TABLE_A[CLIENT]),
FILTER(
TABLE_B, TABLE_B[SUBJECT] = TABLE_A[SUBJECT]))

If someone knows another way to perform this kind of calculation would be highly appreciated.

Regards

EDIT:
It seems that the calculation is running well if the last condition (subject) is not performed. What could cause the program to calculate forever (a blank, a subject not existing in the other table, etc..) ?

[Anonymous]

Hi Clara,

Sorry for late reply.

I can see the computation is going faster with your tips and seems to be working now.

Thanks for your help !

[Anonymous · Accepted solution]

Hi @Anonymous ,

1. Please try to connect the filter conditions using AND (&&) logical operator (which means all conditions must be TRUE), similar to the following.

HAS_TRADED_15D = CALCULATE(
DISTINCTCOUNT(TABLE_B[TRADE_ID]),
FILTER(
TABLE_B, TABLE_B[SUBJECT] = TABLE_A[SUBJECT] && TABLE_B[CLIENT] = TABLE_A[CLIENT] && TABLE_B[TRADE_DATE] >= TABLE_A[INTERACTION_DATE] && TABLE_B[TRADE_DATE] <= TABLE_A[INTERACTION_DATE]+15)
)

2. When you add multiple functions in a statement, especially in two tables, it significantly increases the computational load. This situation is exacerbated if the field has many unique values, or if the data between the two tables does not match. This may be the reason why your calculation is taking longer. If adding the last condition returns blank, it may be that your table does not have data that meets the conditions. You can manually create some data that meets the conditions to verify whether the expression calculation is correct.

If your problem still exists, please provide more detailed information, such as the relationship between tables, specific information about TRADE_DATE and INTERACTION_DATE, etc., so that we can better help you solve the problem.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.