Calculated dimension using rank and concat

[Anonymous]

Hello,

This is a follow up to Variable expansion, quotes and equal sign‌.

Here is my data:

LOAD * INLINE [

  location, id

  siteA, 1

  siteB, 2

  siteC, 3

  siteD, 4

];

If I want a dimension that is a list of all locations, I can either define it in the dimension: ='$(=Concat(location, ', '))'

Or I can create a variable SET vTest= =Concat(location, ', ') and the dimension defined as =vTest

Now, I want to limit how many locations I see to the top two. I know that I can use rank to limit my set like this: =if(aggr(rank(min(id)),location)<=2,location) , but I can't seem to find a way to combine this with Concat to use in a calculated dimension

The result I'm looking for is siteC, siteD. (Order is not important)

Also, is there a way to do this using set expression instead of if statement? My real data is very large and I expect performance will be an issue.

Thanks,

Tanya

[antoniotiman]

Hi Tanya,

it could be

Concat({<id={"=Rank(id)<=2"}>} location,'|')

but You can't use in calculated dimension.

Regards,

Antonio

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this: =Concat({<location={"=rank(min(id))<=2"}>}location, ', ')