Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a function as a measure in a text box:
FirstSortedValue(Distinct [Site Name], Aggr(Count([ID]),[Site Name]))
that works and returns the Site with the lowest count of the ID variable. However, If I try to reverse this using either of the following methods:
FirstSortedValue(Distinct [Site Name], -Aggr(Count([ID]),[Site Name]))
OR
FirstSortedValue(Distinct [Site Name], -1 * Aggr(Count([ID]),[Site Name]))
I get a NULL return (i.e. a small dash and no result). Is anyone else to determine why?
I attach a sample of the data in question.
Thanks
i try and work witch FirstSortedValue(Distinct [Site Name], -Aggr(Count([ID]),[Site Name]))
i try and work witch FirstSortedValue(Distinct [Site Name], -Aggr(Count([ID]),[Site Name]))
Seems to be working for the sample provided
Hi all,
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]))
it works.
Thanks to all