I feel like this shouldn't be so complicated but I can't seem to come to a working formula.
I have some sample data like below. CustomerID and AccountID have a datatype of text. I'd like to create a calculated column that counts the number of times the CustomerID appears in the table.
When I try the formula below, it just returns 8 in every row.
Occurrence =
COUNTAX (
FILTER ( TABLE1, EARLIER ( TABLE1[CustomerID] ) = TABLE1[CustomerID] ),
TABLE1[CustomerID]
)
| CustomerID | AccountID | Occurrence (Expected Output) |
| 111 | 12345 | 3 |
| 111 | 112233 | 3 |
| 111 | 115511 | 3 |
| 222 | 4959 | 2 |
| 222 | 44777 | 2 |
| 333 | 11458 | 1 |
| 444 | 495666 | 1 |
I was able to get a measure working when placed in a table visual with the formula: CALCULATE(COUNTROWS(Table1), ALLSELECTED(Table1), VALUES(Table1[CustomerID])) but I need to have a calculated column in the source table as well.
Appreciate any help
