Ignore all selections in aggregation

[Mauritz_SA]

Hi all

I would like to get a concatenated list of currency codes where the only conversion rate of the currency is 1 and this list should stay constant regardless of selection.

For example, say I have the following table:

[Currencies]:
Load * INLINE [
ID,Currency Code,Conversion Rate
1,USD, 1
2,USD, 0.9
3,EUR, 1
4,EUR, 1
5,POUND, 1
6,POUND, 1
];

I would like to get an output of EUR|POUND in a KPI object (eventually I want to use it in a variable input extension). What I have at the moment is:

=Concat(Distinct If(AGGR(Count({1} DISTINCT [Conversion Rate])=1 AND Avg({1}[Conversion Rate])=1,[Currency Code]),[Currency Code]),'|')

This works fine when I don't make any selections, but the output changes when I make selections. Any help would be much appreciated.

Regards,

Mauritz

@sunny_talwar @Gysbert_Wassenaar @jonathandienst 

[sunny_talwar · Accepted solution]

See if this works

=Concat(DISTINCT {1<[Currency Code] = e({1<[Conversion Rate] -= {'1'}>})>} [Currency Code]), '|')

[Mauritz_SA]

Thanks @sunny_talwar ! I didn't realise that you could use Set Analysis in the Concat Function. Appreciate it!