Ignore all selections in aggregation

Mauritz_SA — Fri, 31 Jan 2020 13:28:13 GMT

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

Re: Ignore all selections in aggregation

sunny_talwar — Fri, 31 Jan 2020 13:34:07 GMT

See if this works

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

Re: Ignore all selections in aggregation

Mauritz_SA — Fri, 31 Jan 2020 14:08:13 GMT

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

topic Re: Ignore all selections in aggregation in App Development

Ignore all selections in aggregation

Re: Ignore all selections in aggregation

Re: Ignore all selections in aggregation