cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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
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])```

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.

2 REPLIES 2
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])```

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.

Highlighted
Regular Visitor

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

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

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors