How to get nationality based on rank

[Anonymous]

Hi Community,

I have the next 3 tables:

I would like to create a textbox which returns the nationality of the most common "Count_NP_ID" (based on rank) if the nationality is "EU".

Can you help me?

Thank you in advance!

Grtz,
Pascal

[Clever_Anjos · Accepted solution]

Try with (please adjust your field names)

FirstSortedValue({<[EU/NOT]={'EU'}>}Nationalteit,Rank)

[avkeep01]

Hi Pascal,

You could try:

=ONLY(IF(AGGR(RANK(COUNT({$<Continent={"EU"}>}Continent)),Continent)=1, Country))

I created an example, so I'm not using your field names. Then it probably will be:

=ONLY(IF(AGGR(RANK(MAX({$<[EU/NOTEU_Empl])={"EU"}>}Count_NP_ID)),[EU/NOTEU_Empl])=1, Nationaliteit))

[Anonymous]

Thanks Clever!