concatenation in calculated dimension

[manojkumargowda]

Hi Guys,

I have a table which resembles like below Emp table. I need to concatenate Dept column with respect to other columns (as shown in expected result) and also country selection in list box should not effect this straight table.  Please let me know how can I achieve this.

Emp:  

Emp Id	Country	Dept
1	US	Sales
1	US	IT
2	IN	Sales
2	IN	HR

Expected result:  

Emp Id	Country	Dept
1	US	Sales, IT
2	IN	Sales, HR

I tried to use Concat function (concat(total <Id> Dept,', ')) and added one more dummy measure (sum({<Country=>}1)) to exclude country selection, but it is not working, as Concat can only be used inside measure.

[manojkumargowda · Accepted solution]

I have found the solution for this, I have used calculated dimension expression as =aggr({<Country=>}concat(Dept,', '),Id).