cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## CountIFS to Calculate Payment Number

Hello,

I have a table (Table A) where I am storing a userID, productID and paymentDate. I would like to use something similar to a countifs statement from excel to add another column with the payment number based on the userID, productID and paymentDate. In excel it would look something like paymentNumber=countifs(userID, 'userID', productID, 'productID', paymentDate, =<'paymentDate'). How do I get do this in power BI to get the Table B below with the new paymentNumber column.

Table A

 userID productID paymentDate a B 1/1/17 a B 1/4/17 a A 1/18/17 a A 1/24/17 a B 2/1/17 b A 1/7/17 b B 1/14/17 b A 1/19/17 b B 1/20/17 b B 1/22/17 c A 1/15/17 c B 1/17/17 c A 1/25/17 c B 1/27/17 c B 1/28/17

Table B

 userID productID paymentDate paymentNumber a B 1/1/17 1 a B 1/4/17 2 a A 1/18/17 1 a A 1/24/17 2 a B 2/1/17 3 b A 1/7/17 1 b B 1/14/17 1 b A 1/19/17 2 b B 1/20/17 2 b B 1/22/17 3 c A 1/15/17 1 c B 1/17/17 1 c A 1/25/17 2 c B 1/27/17 2 c B 1/28/17 3

1 ACCEPTED SOLUTION
Employee

@jeffs9876,

In Power BI Desktop, create a calculated column using the following DAX.

`paymentNumber = CALCULATE(COUNTA(TableA[productID]),FILTER(TableA,TableA[userID]=EARLIER(TableA[userID])&&TableA[productID]=EARLIER(TableA[productID])&&TableA[paymentDate]<=EARLIER(TableA[paymentDate]))) `

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Employee

@jeffs9876,

In Power BI Desktop, create a calculated column using the following DAX.

`paymentNumber = CALCULATE(COUNTA(TableA[productID]),FILTER(TableA,TableA[userID]=EARLIER(TableA[userID])&&TableA[productID]=EARLIER(TableA[productID])&&TableA[paymentDate]<=EARLIER(TableA[paymentDate]))) `

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member
Thank you

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors