Forum Discussion
New column calculating between two tables
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 | A | |
| 01/01/2023 | XXX1 | CLIENT_1 | B | |
| 01/01/2023 | XXX2 | CLIENT_2 | 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..) ?
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.
2 Replies
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.