Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the two following tables (each has hundreds of thousands of observations):
Table 1 (example)
Trans_ID Cust_ID Date_of_trans Reject_Flag
1 10 6/28 0
2 11 6/27 1
3 13 6/26 0
4 10 6/26 1
...
Table 2 (example)
Cust_ID Date_of_first_rejection
11 6/27
10 6/26
15 6/13
...
The two tables are connected via the customer ID. In table one the Trans_ID is the unique identifier and in Table 2 the cust_ID is the unique identifier. There are many instances of the same customer in table one since they can have many transactions on many different dates. What I need to do is to create two new columns in table 2, one which counts the number of transactions (Trans_ID) that a customer had before the date of their first rejection and another column that does the same but for transactions after the date of their first hold.
I have been trying to use calculate and count functions but cant get the right numbers. Is this even possible to do in power bi?
Solved! Go to Solution.
Hi @LDMARTZ
In Sheet1 (Table1), create new columns
first_date = RELATED(Sheet2[Date_of_first_rejection]) after/before = IF([Date_of_trans ]>[first_date],"after","before") Column_before1 = CALCULATE(COUNT(Sheet1[Trans_ID ]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Cust_ID ]),Sheet1[after/before]="before")) count_after = CALCULATE(COUNT(Sheet1[Trans_ID ]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Cust_ID ]),Sheet1[after/before]="after"))
In Sheet2(Table2), create new column
Column = LOOKUPVALUE(Sheet1[Column_before1],Sheet1[Cust_ID ],Sheet2[Cust_ID]) count_after_1 = LOOKUPVALUE(Sheet1[count_after],Sheet1[Cust_ID ],Sheet2[Cust_ID])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @LDMARTZ
In Sheet1 (Table1), create new columns
first_date = RELATED(Sheet2[Date_of_first_rejection]) after/before = IF([Date_of_trans ]>[first_date],"after","before") Column_before1 = CALCULATE(COUNT(Sheet1[Trans_ID ]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Cust_ID ]),Sheet1[after/before]="before")) count_after = CALCULATE(COUNT(Sheet1[Trans_ID ]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Cust_ID ]),Sheet1[after/before]="after"))
In Sheet2(Table2), create new column
Column = LOOKUPVALUE(Sheet1[Column_before1],Sheet1[Cust_ID ],Sheet2[Cust_ID]) count_after_1 = LOOKUPVALUE(Sheet1[count_after],Sheet1[Cust_ID ],Sheet2[Cust_ID])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the previous help. I have come across another related question:
How would I get the same two counts (counts before date of first hold and date after first hold) but for only 30 days before and 30 days after the first hold?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.