Thanks for your replies. It seems the problem was something in the data model. In my actual data I have something like this
ID Site Code Site Name 1 1003 Site C 2 1004 Site D 3 1002 Site B 4 1004 Site D
where Site Name was loaded from a different table and linked up using Site Code as the key. Site Name has some NULLS from some incomplete data. Site Code is fully complete.
When I do a count using
FirstSortedValue(Distinct [Site Name], -Aggr(Count([ID]),[Site Code]))
Thanks to all