Calculated Column To Count Occurrences in Column

GIantCow — Fri, 17 Mar 2023 19:02:53 GMT

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

Re: Calculated Column To Count Occurrences in Column

CNENFRNL — Sat, 18 Mar 2023 02:11:17 GMT

topic Calculated Column To Count Occurrences in Column in DAX Commands and Tips

Calculated Column To Count Occurrences in Column

Re: Calculated Column To Count Occurrences in Column