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.
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 |
Solved! Go to Solution.
@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,
@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,
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.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |