Expression to Get Count with a condition

[sqlpuzzle]

AccountId	Country
1	USA
2	USA
3	USA
4	USA
5	USA
6	USA
7	USA
8	USA
2	CAN
2	CAN
3	CAN
4	CAN
6	CAN
6	CAN
5	USA
9	CAN

Hello,

In a sample scenario I have the above table.

AccountId's in both Countries are 2,3,4,6.

I need an expression that would count the distinct AccountId's that exist in both countries.

In this case I am expecting 4 as four of them (2,3,4,6 exists in both countries)

How can I get this done?

[antoniotiman · Accepted solution]

Hi Joe,

=Count({<AccountId={'=Count(DISTINCT Country) > 1'}>} DISTINCT AccountId)    ->  4

=Concat({<AccountId={'=Count(DISTINCT Country) > 1'}>} DISTINCT AccountId,';')  ->   2;3;4;6

Regards,

Antonio