cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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

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? 

  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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])

1.png

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.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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

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])

1.png

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.

View solution in original post

Highlighted
Regular Visitor

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

@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? 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors