Can I create columns with the counts of instances of an ID in one table based on another table?

[LDMARTZ]

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? 

[v-juanli-msft · Accepted 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.

[LDMARTZ]

@v-juanli-msft 

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?